PDA

View Full Version : Solved: sort without worksheet?



paul_0722
07-15-2008, 06:34 PM
Is it possible to use the sort code shown in this file *without* loading the array from a worksheet? I cannot seem to get the sort to work now using this approach...
Many thanks for previous help to this sort after loading the data from the worksheet, but it seems to fail now when I load the data via code.

Any help appreciated

Bob Phillips
07-15-2008, 11:54 PM
Private Sub cmdSTART_Click()

''Sort a 2 dimensional array on 1 column
''This example sorts a two dimensional array named ArrayName on the first column
''(column 0). The sort is ascending. Reverse the > sign in the fourth row for a
''descending sort. bubble sort

Dim ArrayName(1 To 5, 1 To 5) As Variant
Dim SortColumn1 As Long
Dim Condition1 As Long
Dim i As Long
Dim j As Long
Dim y As Long
Dim t As Variant
Dim x As Long
Dim z As Long
Dim u As Long
Dim v As Long
Dim LastRow As Long
Dim LastCol As Long

'-------------------------------------------------------------------------------
'Read Array
'-------------------------------------------------------------------------------

ArrayName(1, 1) = TimeValue("9:45")
ArrayName(1, 2) = "one"
ArrayName(1, 3) = "two"
ArrayName(1, 4) = "three"
ArrayName(1, 5) = "four"
ArrayName(2, 1) = TimeValue("10:22")
ArrayName(2, 2) = "one"
ArrayName(2, 3) = "two"
ArrayName(2, 4) = "three"
ArrayName(2, 5) = "four"
ArrayName(3, 1) = TimeValue("7:46")
ArrayName(3, 2) = "one"
ArrayName(3, 3) = "two"
ArrayName(3, 4) = "three"
ArrayName(3, 5) = "four"
ArrayName(4, 1) = TimeValue("1:33")
ArrayName(4, 2) = "one"
ArrayName(4, 3) = "two"
ArrayName(4, 4) = "three"
ArrayName(4, 5) = "four"
ArrayName(5, 1) = TimeValue("23:57")
ArrayName(5, 2) = "one"
ArrayName(5, 3) = "two"
ArrayName(5, 4) = "three"
ArrayName(5, 5) = "four"

''LastRow = Range("A1").End(xlDown).Row
''LastCol = Range("A1").End(xlToRight).Column
''ReDim ArrayName(1 To LastRow, 1 To LastCol)
''Sheets("input_array").Activate
''z = 1
''y = 1
''Do
'' Do While Sheets("input_array").Cells(z, y).Value <> ""
'' If Sheets("input_array").Cells(z, y).Value <> "" Then
'' ArrayName(z, y) = Sheets("input_array").Cells(z, y).Value
'' y = y + 1
'' End If
'' Loop
'' z = z + 1
'' y = 1
'' If Cells(z, y).Value = "" Then Exit Do
''Loop
'-------------------------------------------------------------------------------
'Confirm array before sort
'-------------------------------------------------------------------------------
For u = 1 To UBound(ArrayName, 1)
For v = 1 To UBound(ArrayName, 2)
Sheets("input_array").Cells(u + 6, v).Value = ArrayName(u, v)
Next v
Next u
'-------------------------------------------------------------------------------
'Sort
'-------------------------------------------------------------------------------
SortColumn1 = 1
For i = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
For j = LBound(ArrayName, 1) To UBound(ArrayName, 1) - 1
Condition1 = ArrayName(j, SortColumn1) > ArrayName(j + 1, SortColumn1)
If Condition1 Then
For y = LBound(ArrayName, 2) To UBound(ArrayName, 2)
t = ArrayName(j, y)
ArrayName(j, y) = ArrayName(j + 1, y)
ArrayName(j + 1, y) = t
Next y
End If
Next
Next
'-------------------------------------------------------------------------------
'Confirm After Sort
'-------------------------------------------------------------------------------
For u = 1 To UBound(ArrayName, 1)
For v = 1 To UBound(ArrayName, 2)
Sheets("input_array").Cells(u + 12, v).Value = ArrayName(u, v)
Next v
Next u

Sheets("input_array").Range("G7").Value = "VERIFY"
Sheets("input_array").Range("G13").Value = "SORTED"

End Sub

paul_0722
07-16-2008, 12:11 AM
That's it. Thanks very much