Solved: Sorting Multi Arrays

[mferrisi]

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

[lucas]

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.

[mferrisi]

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

[Bob Phillips]

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]

[mferrisi]

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

[Bob Phillips]

Try using date formats such as

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

[mferrisi]

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.

[Bob Phillips]

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)