Solved: Sorting Multi Arrays

I have imported data into an array that is 1 to 55, 1 to four, and Array(i, 1) contains a date. I need to sort the entire array in order by date, so that all corresponding cells are also sorted (ie Array(i, 2) moves with Array(i, 1)

thank you

Matt

Hi Matt,
You should post a workbook with before and after results along with what you have so far with the array code....I for one do not understand what your trying to do.

I pull returns and the corresponding dates from a database using
TempW = sqlrequest(quesrystring, databasename, etc , , , )

TempW becomes 1 to 4, 1 to 55
Tempw(i, 1) is a date
Tempw(i, 2) is a return.

I need to sort the array rows by date.

Please let me know if that helps.
Thank you

Matt

Here is a 2D bubble sort I use. You should be able to adapt it


[vba]
Function BubbleSort2D(ByVal List As Variant, _
ByVal SortCol As Long, _
Optional ByVal SortColNumeric As Boolean = False, _
Optional ByVal Order As XlSortOrder = xlAscending) As Variant
' Sorts an array using bubble sort algorithm
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer, k As Long
Dim iColumn
Dim Temp
First = LBound(List, 1)
Last = UBound(List, 1)
iColumn = LBound(List, 2) + SortCol - 1
For i = First To Last - 1
For j = i + 1 To Last
If Order = xlAscending Then
If SortColNumeric Then
If CDbl(List(i, iColumn)) > CDbl(List(j, iColumn)) Then
For k = LBound(List, 2) To UBound(List, 2)
Temp = List(j, k)
List(j, k) = List(i, k)
List(i, k) = Temp
Next k
End If
Else
If List(i, iColumn) > List(j, iColumn) Then
For k = LBound(List, 2) To UBound(List, 2)
Temp = List(j, k)
List(j, k) = List(i, k)
List(i, k) = Temp
Next k
End If
End If
Else
If SortColNumeric Then
If CDbl(List(i, iColumn)) < CDbl(List(j, iColumn)) Then
For k = LBound(List, 2) To UBound(List, 2)
Temp = List(j, k)
List(j, k) = List(i, k)
List(i, k) = Temp
Next k
End If
Else
If List(i, iColumn) < List(j, iColumn) Then
For k = LBound(List, 2) To UBound(List, 2)
Temp = List(j, k)
List(j, k) = List(i, k)
List(i, k) = Temp
Next k
End If
End If
End If
Next j
Next i
BubbleSort2D = List
End Function
[/vba]

Thanks. The sort method seems to work, but I think that I need to change the date format in TempW(i, 1) since my array sorts to
TempW(1,1) = 1/31/2004
TempW(2,1) = 1/31/2005
TempW(3,1) = 1/31/2006
TempW(4,1) = 1/31/2007
TempW(5,1) = 10/31/2004
..
..
TempW(9, 1)= 11/30/2004

etc etc

I tried to convert the dates in the array to the general format, but I keep returning a 'False' or '12:00:00 am' when I do. e.g.

TempW(q, 1) = FormatDateTime(TempW(q, 1)) = "General Date"

What do I need to do to get around this?

Thank you

Try using date formats such as

TempW(1,1) = "2004-01-31"

My dates come in via database query, so in the above code I'm just showing what is held in the array when it is imported.

What I need is something like:
Tempw(n, 1) = Tempw(n,1) with dateformat that can be sorted.

I am guessing a bit as I don't have US settings, but can you use

Temp(n, 1).Text = DateValue(Temp(1, 1).Text)