redsun001
10-31-2017, 01:56 PM
Hi all,
If I have been given data in Range("E1:F10") on excel worksheet and I wanted to sort this array out. I have used the Code given below and it works for sorting the array out in order but i am struggling to print the sorted array out in different column; let's Range("A1:A10) on the same worksheet. When I do run this sub, I end up with #N/A in all of the cells for "A1:A10"
Can someone please have a look at it and let me know where I am making a mistake.
Ps: New to VBA so apology if this seems like a minor issue. I tried many loops but ended up with error one way or another.
Thank you.
VBA Code:
Sub Sort_2D_Array()
Dim v As Variant
Dim i As Integer, j As Integer, ci As Integer
Dim r As Integer, c As Integer
Dim Temp As Variant
Dim myarray() As Variant
'populate array
myarray() = Range("E1:F10").Value2
'Bubble sort
ci = LBound(myarray, 2) '1st column index
For i = LBound(myarray) To UBound(myarray) - 1
For j = i + 1 To UBound(myarray)
If myarray(i, ci) > myarray(j, ci) Then
For c = LBound(myarray, 2) To UBound(myarray, 2)
Temp = myarray(i, c)
myarray(i, c) = myarray(j, c)
myarray(j, c) = Temp
Next
End If
Next
Next
For i = 1 to 10
For j = 1 to 10
Range("A1:B10") = Application.Transpose(myarray)
Next j
Next i
End Sub
If I have been given data in Range("E1:F10") on excel worksheet and I wanted to sort this array out. I have used the Code given below and it works for sorting the array out in order but i am struggling to print the sorted array out in different column; let's Range("A1:A10) on the same worksheet. When I do run this sub, I end up with #N/A in all of the cells for "A1:A10"
Can someone please have a look at it and let me know where I am making a mistake.
Ps: New to VBA so apology if this seems like a minor issue. I tried many loops but ended up with error one way or another.
Thank you.
VBA Code:
Sub Sort_2D_Array()
Dim v As Variant
Dim i As Integer, j As Integer, ci As Integer
Dim r As Integer, c As Integer
Dim Temp As Variant
Dim myarray() As Variant
'populate array
myarray() = Range("E1:F10").Value2
'Bubble sort
ci = LBound(myarray, 2) '1st column index
For i = LBound(myarray) To UBound(myarray) - 1
For j = i + 1 To UBound(myarray)
If myarray(i, ci) > myarray(j, ci) Then
For c = LBound(myarray, 2) To UBound(myarray, 2)
Temp = myarray(i, c)
myarray(i, c) = myarray(j, c)
myarray(j, c) = Temp
Next
End If
Next
Next
For i = 1 to 10
For j = 1 to 10
Range("A1:B10") = Application.Transpose(myarray)
Next j
Next i
End Sub