Solved: sort will not sort - why?

[paul_0722]

Can anyone spot why this simple bubble sort will not act on the simple array I have given it? It is supposed to sort on first column but fails to do so. I'm doing something wrong I'm sure... but cannot find it.

Any help appreciated...

Thanks

[vba]
Option Explicit
Sub testsort()
''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 trip() As Variant
Dim u As Long
Dim v As Long

Dim SortColumn1 As Long
Dim Condition1 As Long
Dim i As Long
Dim j As Long
Dim y As Long
Dim t As Variant
'-------------------------------------------------------------------------------
'Read Array
'-------------------------------------------------------------------------------
ReDim trip(1 To 5, 1 To 4)

trip(1, 1) = -0.016667
trip(2, 1) = "Red"
trip(3, 1) = "7:19"
trip(4, 1) = "6:35"
trip(5, 1) = 1
trip(1, 2) = 0.05625
trip(2, 2) = "Blue"
trip(3, 2) = "7:19"
trip(4, 2) = "06:40"
trip(5, 2) = 1
trip(1, 3) = 0.021528
trip(2, 3) = "Red"
trip(3, 3) = "7:19"
trip(4, 3) = "06:52"
trip(5, 3) = 2
trip(1, 4) = -0.004167
trip(2, 4) = "Blue"
trip(3, 4) = "7:19"
trip(4, 4) = "08:20"
trip(5, 4) = 3
'-------------------------------------------------------------------------------
'Confirm array in two different ways before sort
'-------------------------------------------------------------------------------
For u = 1 To UBound(trip, 2)
Sheets("sort1").Cells(u, 1).Value = trip(1, u) 'calculate time
Sheets("sort1").Cells(u, 2).Value = trip(2, u) 'stop
Sheets("sort1").Cells(u, 3).Value = trip(3, u) 'stop_time_now
Sheets("sort1").Cells(u, 4).Value = trip(4, u) 'time
Sheets("sort1").Cells(u, 5).Value = trip(5, u) 'trip number
Sheets("sort1").Cells(u, 6).Value = u 'index 1
Next u
For u = 1 To UBound(trip, 1)
For v = 1 To UBound(trip, 2)
Sheets("alt_sort1").Cells(v, u).Value = trip(u, v)
Sheets("alt_sort1").Cells(v, u + 1).Value = u
Sheets("alt_sort1").Cells(v, u + 2).Value = v
Next v
Next u

'-------------------------------------------------------------------------------
'Sort
'-------------------------------------------------------------------------------
SortColumn1 = 1
For i = LBound(trip, 1) To UBound(trip, 1) - 1
For j = LBound(trip, 1) To UBound(trip, 1) - 1
Condition1 = trip(j, SortColumn1) > trip(j + 1, SortColumn1)
If Condition1 Then
For y = LBound(trip, 2) To UBound(trip, 2)
t = trip(j, y)
trip(j, y) = trip(j + 1, y)
trip(j + 1, y) = t
Next y
End If
Next
Next
'-------------------------------------------------------------------------------
'Confirm in two different ways After Sort
'-------------------------------------------------------------------------------
For v = 1 To UBound(trip, 2)
Sheets("sort2").Cells(v, 1).Value = trip(1, v) 'calculate time
Sheets("sort2").Cells(v, 2).Value = trip(2, v) 'stop
Sheets("sort2").Cells(v, 3).Value = trip(3, v) 'stop_time_now
Sheets("sort2").Cells(v, 4).Value = trip(4, v) 'time
Sheets("sort2").Cells(v, 5).Value = trip(5, v) 'trip number
Sheets("sort2").Cells(v, 6).Value = v 'index 1
Next v
For u = 1 To UBound(trip, 1)
For v = 1 To UBound(trip, 2)
Sheets("alt_sort2").Cells(v, u).Value = trip(u, v)
Sheets("alt_sort2").Cells(v, u + 1).Value = u
Sheets("alt_sort2").Cells(v, u + 2).Value = v
Next v
Next u
End Sub

[/vba]

[Bob Phillips]

I think you have got your columns and rows mixed up. You have 4 rows, but have loaded 5.

[paul_0722]

Thanks very much for good advice as always, but if you'll clarify just a few things for me...

By "load five rows" I think you are referring to the first index of the multi dimension array. If I rewrite the data to fit in array test(4,5) then that will be the right number of columns to send into the sort?

The reason I ask is that to redimension a multidim VBA array apparently you can only redim the second index and that is what I have done prior to this code and why it is now test(5,4). To use the sort apparently I should reverse the data now to create test(4,5) and that will sort properly?

Thanks again - really appreciate all your good help to date!

[Bob Phillips]

Thanks very much for good advice as always, but if you'll clarify just a few things for me...

By "load five rows" I think you are referring to the first index of the multi dimension array. If I rewrite the data to fit in array test(4,5) then that will be the right number of columns to send into the sort?

Yes that is right, think of an 2D array as row and columns, just like a range.

The reason I ask is that to redimension a multidim VBA array apparently you can only redim the second index and that is what I have done prior to this code and why it is now test(5,4). To use the sort apparently I should reverse the data now to create test(4,5) and that will sort properly?

I am not sure what point you are raising here. You are correct that you can only redimension the upper dimension of an array, but that is when relates to dynamically changing an array. This code

[vba]
Dim trip() As Variant

ReDim trip(1 To 4, 1 To 5)
[/vba]

changes the size of both dimensions, or more accurately, it sets the size of both dimensions because the initial Dim is really just a placeholder for the array, the memory is not allocated until you Redim

But the dynamic Redim is only really necessary when you don't know how big the array will be. In your example, you do, so you can use

[vba]
Dim trip(1 To 4, 1 To 5)

trip(1, 1) = -0.016667
'etc.
[/vba]

[paul_0722]

Thanks! That makes it clear and I've tried it out and proved it works. My problem is that my real application (not this sample code) does begin with a dynamic array and uses the ReDim statement on the upper dimension. By the time it makes it to this section of code it will be necessary to switch the dimensions in order to use this sort routine.

Or, to explain it another way... there are 100+ columns in the beginning data array which are ReDim'ed down to less than 100. So say the first array is bus(5,200) and after ReDim it is bus(5,67) - or whatever 2nd number is right - I cannot tell in advance. The *next* step is the sort and apparently the only way to ensure success with it is to make it into bus(67,5) and then send it in for sorting. I'll try this approach now and am reasonably certain of success.

Thanks so much for your kind and valuable help.

[mdmackillop]

Hi Paul,
I'm a little confused with your initial "Confim Arrays" as you are not "plotting" it as it is stored. Is this intentional?
In any case, you can simplify the writing of the array by transposing.

[vba]
Option Explicit
Sub testsort2()
''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 trip() As Variant
Dim u As Long
Dim v As Long

Dim SortColumn1 As Long
Dim Condition1 As Long
Dim i As Long
Dim j As Long
Dim y As Long
Dim t As Variant
'-------------------------------------------------------------------------------
'Read Array
'-------------------------------------------------------------------------------
ReDim trip(1 To 5, 1 To 4)

trip(1, 1) = -0.016667
trip(2, 1) = "Red"
trip(3, 1) = "7:19"
trip(4, 1) = "6:35"
trip(5, 1) = 1
trip(1, 2) = 0.05625
trip(2, 2) = "Blue"
trip(3, 2) = "7:19"
trip(4, 2) = "06:40"
trip(5, 2) = 1
trip(1, 3) = 0.021528
trip(2, 3) = "Red"
trip(3, 3) = "7:19"
trip(4, 3) = "06:52"
trip(5, 3) = 2
trip(1, 4) = -0.004167
trip(2, 4) = "Blue"
trip(3, 4) = "7:19"
trip(4, 4) = "08:20"
trip(5, 4) = 3

'array as entered
For u = 1 To UBound(trip, 1)
For v = 1 To UBound(trip, 2)
Sheets("Normal").Cells(u, v).Value = trip(u, v)
Next v
Next u


trip = Application.Transpose(trip)

'transposed array
For u = 1 To UBound(trip, 1)
For v = 1 To UBound(trip, 2)
Sheets("transposed").Cells(u, v).Value = trip(u, v)
Next v
Next u

End Sub
[/vba]

[Bob Phillips]

Thanks! That makes it clear and I've tried it out and proved it works. My problem is that my real application (not this sample code) does begin with a dynamic array and uses the ReDim statement on the upper dimension. By the time it makes it to this section of code it will be necessary to switch the dimensions in order to use this sort routine.

Or, to explain it another way... there are 100+ columns in the beginning data array which are ReDim'ed down to less than 100. So say the first array is bus(5,200) and after ReDim it is bus(5,67) - or whatever 2nd number is right - I cannot tell in advance. The *next* step is the sort and apparently the only way to ensure success with it is to make it into bus(67,5) and then send it in for sorting. I'll try this approach now and am reasonably certain of success.

Thanks so much for your kind and valuable help.

You could always turn the sort macro around

[vba]

'-------------------------------------------------------------------------------
'Sort
'-------------------------------------------------------------------------------
SortColumn1 = 1
For i = LBound(trip, 2) To UBound(trip, 2) - 1
For j = LBound(trip, 2) To UBound(trip, 2) - 1
Condition1 = trip(SortColumn1, j) > trip(SortColumn1, j + 1)
If Condition1 Then
For y = LBound(trip, 1) To UBound(trip, 1)
t = trip(y, j)
trip(y, j) = trip(y, j + 1)
trip(y, j + 1) = t
Next y
End If
Next
Next
[/vba]

[paul_0722]

Thanks for replies on transpose and revised sort routine - both work just as I had hoped!

Thanks again