Hi I am using a BubbleSort function as per:
I am passing an array of Variant type which I know is loaded with daily data across two month (i.e. 59 rows of data and 8 columns). However when the function completes. a portion of the data is missing in the sorted array. Clearly all the data in January.
Any ideas what I am doing wrong here?
***********
Sub test()
'Load MonthDayArray
...code that fully loads array and prints output to Immediate Window (See Below)
'Sort Array
SortedArray = XBubbleSort(MonthDayArray, 4, True)
End Sub
************
Function XBubbleSort(UnsortedArray As Variant, keyColumn As Integer, Optional SortDescending As Boolean) As Variant
Dim ArrayOfRows() As Long
Dim SortedArray() As Variant
Dim maxRow As Long, maxColumn As Long
Dim i As Long, j As Long, temp As Variant
maxRow = UBound(UnsortedArray, 1)
maxColumn = UBound(UnsortedArray, 2)
Rem create array of rows
ReDim ArrayOfRows(1 To maxRow)
For i = 1 To maxRow
ArrayOfRows(i) = i
Next i
Rem sort ArrayOfRows
For i = 1 To maxRow - 1 'was "- 1"?
For j = i + 1 To maxRow
If UnsortedArray(ArrayOfRows(j), keyColumn) < UnsortedArray(ArrayOfRows(i), keyColumn) Xor SortDescending Then
temp = ArrayOfRows(i)
ArrayOfRows(i) = ArrayOfRows(j)
ArrayOfRows(j) = temp
End If
Next j
Next i
Debug.Print "Sorted Results"
Rem make sorted array
ReDim SortedArray(1 To maxRow, 1 To maxColumn)
For i = 1 To maxRow
For j = 1 To maxColumn
SortedArray(i, j) = UnsortedArray(ArrayOfRows(i), j)
Next j
Debug.Print SortedArray(i, 1) & "," & SortedArray(i, 2) & ","; Format(SortedArray(i, 3), "#,##0") _
& ","; Format(SortedArray(i, 4), "#,##0") & ","; Format(SortedArray(i, 5), "#,##0") & ","; Format(SortedArray(i, 6), "#,##0") & ","; Format(SortedArray(i, 7), "#,##0") _
& ","; Format(SortedArray(i, 8), "#,##0")
Next i
XBubbleSort = SortedArray
End Function
*************
Unsorted Results
1,1,18,-216,1,576,-56,981,1,558,-57,197
1,2,-1,27,2,879,-95,880,2,880,-95,853
1,3,-1,25,2,145,-72,856,2,146,-72,832
1,4,-1,26,1,837,-61,771,1,838,-61,745
1,5,-1,26,1,965,-63,897,1,966,-63,871
1,6,-1,24,2,342,-75,082,2,343,-75,057
1,7,-1,27,2,488,-79,706,2,489,-79,679
1,8,-1,26,2,122,-69,775,2,123,-69,749
1,9,-1,26,1,877,-60,477,1,878,-60,451
1,10,93,-1,440,1,635,-58,663,1,543,-60,103
1,11,571,-8,528,180,-7,236,-391,-15,764
1,12,-1,-61,1,356,-322,824,1,357,-322,885
1,13,-1,24,2,289,-78,114,2,290,-78,090
1,14,-1,-3,1,869,-95,852,1,870,-95,856
1,15,285,-3,776,1,458,-50,928,1,173,-54,704
1,16,401,-6,688,1,503,-125,779,1,102,-132,467
1,17,136,-2,292,940,-29,964,804,-32,256
1,18,704,-13,019,581,-16,395,-123,-29,414
1,19,668,-13,571,15,-403,-653,-13,974
1,20,282,-5,836,1,445,-45,364,1,163,-51,200
1,21,-1,29,2,238,-65,832,2,239,-65,803
1,22,-1,29,2,554,-76,628,2,555,-76,599
1,23,-1,28,2,412,-75,767,2,413,-75,739
1,24,2,-13,2,319,-72,636,2,317,-72,649
1,25,-1,26,3,206,-103,044,3,207,-103,019
1,26,12,-232,1,646,-53,447,1,634,-53,679
1,27,0,-2,1,551,-51,125,1,551,-51,127
1,28,474,-7,100,315,-9,812,-159,-16,913
1,29,904,-13,795,3,-111,-901,-13,906
1,30,66,-1,312,1,354,-43,467,1,289,-44,779
1,31,2,037,-30,013,-45,1,565,-2,082,-28,448
2,1,-1,24,2,189,-76,639,2,190,-76,616
2,2,-1,24,2,073,-72,183,2,074,-72,159
2,3,-1,29,1,960,-57,824,1,961,-57,795
2,4,-1,28,2,477,-77,187,2,478,-77,158
2,5,-1,28,2,574,-80,738,2,575,-80,710
2,6,-1,30,1,821,-54,490,1,822,-54,460
2,7,-1,27,2,046,-65,413,2,047,-65,386
2,8,-1,22,2,553,-93,135,2,554,-93,114
2,9,185,-3,081,1,422,-48,411,1,236,-51,492
2,10,242,-4,975,1,048,-30,119,806,-35,094
2,11,447,-7,504,365,-11,593,-82,-19,097
2,12,785,-14,958,92,-3,058,-694,-18,016
2,13,83,-1,884,1,130,-34,695,1,047,-36,579
2,14,380,-6,439,1,218,-33,348,839,-39,787
2,15,628,-14,379,52,-1,604,-576,-15,983
2,16,529,-10,577,207,-5,333,-321,-15,910
2,17,230,-4,294,984,-31,677,755,-35,971
2,18,-1,28,2,837,-84,481,2,838,-84,453
2,19,15,-344,2,081,-66,401,2,067,-66,744
2,20,106,-1,814,1,718,-64,215,1,613,-66,029
2,21,-1,22,2,862,-95,887,2,863,-95,865
2,22,6,-122,2,350,-77,779,2,344,-77,901
2,23,-1,26,2,664,-77,116,2,665,-77,090
2,24,-1,27,2,407,-67,209,2,408,-67,182
2,25,-1,28,2,475,-64,974,2,476,-64,946
2,26,99,-2,340,1,507,-44,263,1,407,-46,604
2,27,-1,26,2,415,-68,111,2,416,-68,085
2,28,-1,25,2,391,-72,639,2,392,-72,614
Sorted Results
2,6,-1,30,1,821,-54,490,1,822,-54,460
2,3,-1,29,1,960,-57,824,1,961,-57,795
2,4,-1,28,2,477,-77,187,2,478,-77,158
2,5,-1,28,2,574,-80,738,2,575,-80,710
2,25,-1,28,2,475,-64,974,2,476,-64,946
2,18,-1,28,2,837,-84,481,2,838,-84,453
2,24,-1,27,2,407,-67,209,2,408,-67,182
2,7,-1,27,2,046,-65,413,2,047,-65,386
2,27,-1,26,2,415,-68,111,2,416,-68,085
2,23,-1,26,2,664,-77,116,2,665,-77,090
2,28,-1,25,2,391,-72,639,2,392,-72,614
2,1,-1,24,2,189,-76,639,2,190,-76,616
2,2,-1,24,2,073,-72,183,2,074,-72,159
2,21,-1,22,2,862,-95,887,2,863,-95,865
2,8,-1,22,2,553,-93,135,2,554,-93,114
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
2,22,6,-122,2,350,-77,779,2,344,-77,901
2,19,15,-344,2,081,-66,401,2,067,-66,744
2,20,106,-1,814,1,718,-64,215,1,613,-66,029
2,13,83,-1,884,1,130,-34,695,1,047,-36,579
2,26,99,-2,340,1,507,-44,263,1,407,-46,604
2,9,185,-3,081,1,422,-48,411,1,236,-51,492
2,17,230,-4,294,984,-31,677,755,-35,971
2,10,242,-4,975,1,048,-30,119,806,-35,094
2,14,380,-6,439,1,218,-33,348,839,-39,787
2,11,447,-7,504,365,-11,593,-82,-19,097
2,16,529,-10,577,207,-5,333,-321,-15,910
2,15,628,-14,379,52,-1,604,-576,-15,983
2,12,785,-14,958,92,-3,058,-694,-18,016
Help With VBA - BUBBLE SORT
Given a table - Number Surname Province Country Gender DOB Salary 187 Fillion ON USA Male 01/10/1980 $144000 3 Simon GA USA Female 07/08/1972 $176000 How would i sort the entire table at the same...
www.excelforum.com
I am passing an array of Variant type which I know is loaded with daily data across two month (i.e. 59 rows of data and 8 columns). However when the function completes. a portion of the data is missing in the sorted array. Clearly all the data in January.
Any ideas what I am doing wrong here?
***********
Sub test()
'Load MonthDayArray
...code that fully loads array and prints output to Immediate Window (See Below)
'Sort Array
SortedArray = XBubbleSort(MonthDayArray, 4, True)
End Sub
************
Function XBubbleSort(UnsortedArray As Variant, keyColumn As Integer, Optional SortDescending As Boolean) As Variant
Dim ArrayOfRows() As Long
Dim SortedArray() As Variant
Dim maxRow As Long, maxColumn As Long
Dim i As Long, j As Long, temp As Variant
maxRow = UBound(UnsortedArray, 1)
maxColumn = UBound(UnsortedArray, 2)
Rem create array of rows
ReDim ArrayOfRows(1 To maxRow)
For i = 1 To maxRow
ArrayOfRows(i) = i
Next i
Rem sort ArrayOfRows
For i = 1 To maxRow - 1 'was "- 1"?
For j = i + 1 To maxRow
If UnsortedArray(ArrayOfRows(j), keyColumn) < UnsortedArray(ArrayOfRows(i), keyColumn) Xor SortDescending Then
temp = ArrayOfRows(i)
ArrayOfRows(i) = ArrayOfRows(j)
ArrayOfRows(j) = temp
End If
Next j
Next i
Debug.Print "Sorted Results"
Rem make sorted array
ReDim SortedArray(1 To maxRow, 1 To maxColumn)
For i = 1 To maxRow
For j = 1 To maxColumn
SortedArray(i, j) = UnsortedArray(ArrayOfRows(i), j)
Next j
Debug.Print SortedArray(i, 1) & "," & SortedArray(i, 2) & ","; Format(SortedArray(i, 3), "#,##0") _
& ","; Format(SortedArray(i, 4), "#,##0") & ","; Format(SortedArray(i, 5), "#,##0") & ","; Format(SortedArray(i, 6), "#,##0") & ","; Format(SortedArray(i, 7), "#,##0") _
& ","; Format(SortedArray(i, 8), "#,##0")
Next i
XBubbleSort = SortedArray
End Function
*************
Unsorted Results
1,1,18,-216,1,576,-56,981,1,558,-57,197
1,2,-1,27,2,879,-95,880,2,880,-95,853
1,3,-1,25,2,145,-72,856,2,146,-72,832
1,4,-1,26,1,837,-61,771,1,838,-61,745
1,5,-1,26,1,965,-63,897,1,966,-63,871
1,6,-1,24,2,342,-75,082,2,343,-75,057
1,7,-1,27,2,488,-79,706,2,489,-79,679
1,8,-1,26,2,122,-69,775,2,123,-69,749
1,9,-1,26,1,877,-60,477,1,878,-60,451
1,10,93,-1,440,1,635,-58,663,1,543,-60,103
1,11,571,-8,528,180,-7,236,-391,-15,764
1,12,-1,-61,1,356,-322,824,1,357,-322,885
1,13,-1,24,2,289,-78,114,2,290,-78,090
1,14,-1,-3,1,869,-95,852,1,870,-95,856
1,15,285,-3,776,1,458,-50,928,1,173,-54,704
1,16,401,-6,688,1,503,-125,779,1,102,-132,467
1,17,136,-2,292,940,-29,964,804,-32,256
1,18,704,-13,019,581,-16,395,-123,-29,414
1,19,668,-13,571,15,-403,-653,-13,974
1,20,282,-5,836,1,445,-45,364,1,163,-51,200
1,21,-1,29,2,238,-65,832,2,239,-65,803
1,22,-1,29,2,554,-76,628,2,555,-76,599
1,23,-1,28,2,412,-75,767,2,413,-75,739
1,24,2,-13,2,319,-72,636,2,317,-72,649
1,25,-1,26,3,206,-103,044,3,207,-103,019
1,26,12,-232,1,646,-53,447,1,634,-53,679
1,27,0,-2,1,551,-51,125,1,551,-51,127
1,28,474,-7,100,315,-9,812,-159,-16,913
1,29,904,-13,795,3,-111,-901,-13,906
1,30,66,-1,312,1,354,-43,467,1,289,-44,779
1,31,2,037,-30,013,-45,1,565,-2,082,-28,448
2,1,-1,24,2,189,-76,639,2,190,-76,616
2,2,-1,24,2,073,-72,183,2,074,-72,159
2,3,-1,29,1,960,-57,824,1,961,-57,795
2,4,-1,28,2,477,-77,187,2,478,-77,158
2,5,-1,28,2,574,-80,738,2,575,-80,710
2,6,-1,30,1,821,-54,490,1,822,-54,460
2,7,-1,27,2,046,-65,413,2,047,-65,386
2,8,-1,22,2,553,-93,135,2,554,-93,114
2,9,185,-3,081,1,422,-48,411,1,236,-51,492
2,10,242,-4,975,1,048,-30,119,806,-35,094
2,11,447,-7,504,365,-11,593,-82,-19,097
2,12,785,-14,958,92,-3,058,-694,-18,016
2,13,83,-1,884,1,130,-34,695,1,047,-36,579
2,14,380,-6,439,1,218,-33,348,839,-39,787
2,15,628,-14,379,52,-1,604,-576,-15,983
2,16,529,-10,577,207,-5,333,-321,-15,910
2,17,230,-4,294,984,-31,677,755,-35,971
2,18,-1,28,2,837,-84,481,2,838,-84,453
2,19,15,-344,2,081,-66,401,2,067,-66,744
2,20,106,-1,814,1,718,-64,215,1,613,-66,029
2,21,-1,22,2,862,-95,887,2,863,-95,865
2,22,6,-122,2,350,-77,779,2,344,-77,901
2,23,-1,26,2,664,-77,116,2,665,-77,090
2,24,-1,27,2,407,-67,209,2,408,-67,182
2,25,-1,28,2,475,-64,974,2,476,-64,946
2,26,99,-2,340,1,507,-44,263,1,407,-46,604
2,27,-1,26,2,415,-68,111,2,416,-68,085
2,28,-1,25,2,391,-72,639,2,392,-72,614
Sorted Results
2,6,-1,30,1,821,-54,490,1,822,-54,460
2,3,-1,29,1,960,-57,824,1,961,-57,795
2,4,-1,28,2,477,-77,187,2,478,-77,158
2,5,-1,28,2,574,-80,738,2,575,-80,710
2,25,-1,28,2,475,-64,974,2,476,-64,946
2,18,-1,28,2,837,-84,481,2,838,-84,453
2,24,-1,27,2,407,-67,209,2,408,-67,182
2,7,-1,27,2,046,-65,413,2,047,-65,386
2,27,-1,26,2,415,-68,111,2,416,-68,085
2,23,-1,26,2,664,-77,116,2,665,-77,090
2,28,-1,25,2,391,-72,639,2,392,-72,614
2,1,-1,24,2,189,-76,639,2,190,-76,616
2,2,-1,24,2,073,-72,183,2,074,-72,159
2,21,-1,22,2,862,-95,887,2,863,-95,865
2,8,-1,22,2,553,-93,135,2,554,-93,114
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
2,22,6,-122,2,350,-77,779,2,344,-77,901
2,19,15,-344,2,081,-66,401,2,067,-66,744
2,20,106,-1,814,1,718,-64,215,1,613,-66,029
2,13,83,-1,884,1,130,-34,695,1,047,-36,579
2,26,99,-2,340,1,507,-44,263,1,407,-46,604
2,9,185,-3,081,1,422,-48,411,1,236,-51,492
2,17,230,-4,294,984,-31,677,755,-35,971
2,10,242,-4,975,1,048,-30,119,806,-35,094
2,14,380,-6,439,1,218,-33,348,839,-39,787
2,11,447,-7,504,365,-11,593,-82,-19,097
2,16,529,-10,577,207,-5,333,-321,-15,910
2,15,628,-14,379,52,-1,604,-576,-15,983
2,12,785,-14,958,92,-3,058,-694,-18,016
