PDA

View Full Version : [SOLVED] How can I print the sorted array back into worksheet?



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

Kenneth Hobs
10-31-2017, 02:15 PM
Please paste code between code tags. Click # icon on toolbar to insert the code tags.

Range("A1:B10").Value = myarray

Test:

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

With Range("E1:F10")
.Formula = "=Rand()"
.Value = .Value
End With

'populate array
myarray() = Range("E1:F10").Value

'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

Range("A1:B10").Value = myarray
End Sub

snb
11-01-2017, 04:12 AM
Sub M_snb()
with range("A1:B10")
.value=range("E1:F10").value
.sort .cells(1)
end with
End Sub

redsun001
11-02-2017, 04:44 AM
Thank you, Kenneth. This was helpful it worked. :)

redsun001
11-02-2017, 04:44 AM
This also worked, snb. Thank you :)