Array Sorting problem

[Steven]

I am trying to make this sort work. I copied it from this forum. I am new
to arrays. I am not sure I am approaching this correctly but I wanted to see
it work so I input data on a worksheet from I7 to J12. Col I is alpha and J
is numeric. When it runs it will return an error at:

X = SortArray((L + R) / 2, col)

the errors says "Subscript out of range"

How do I fix this? Is it saying the SortArray is not setup?


Thank you,

Steven


Sub aaTesterSort()
Dim bAscending As Boolean
Set rng = Range("I7").CurrentRegion
vArr = rng.Value
bAscending = False
QuickSort vArr, 5, LBound(vArr, 1), UBound(vArr, 1), bAscending

' vArr now holds a sorted verion of itself
Range("I26").Resize(UBound(vArr, 1), UBound(vArr, 2)).Value = vArr
End Sub


Sub QuickSort(SortArray, col, L, R, bAscending)
'
'Originally Posted by Jim Rech 10/20/98 Excel.Programming

' Modifications made by t.w. ogilvy
'Modified to sort on first column of a two dimensional array
'Modified to handle a second dimension greater than 1 (or zero)
'Modified to do Ascending or Descending
Dim i, j, X, Y, mm


i = L
j = R
X = SortArray((L + R) / 2, col)
If bAscending Then
While (i <= j)
While (SortArray(i, col) < X And i < R)
i = i + 1
Wend
While (X < SortArray(j, col) And j > L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
Else
While (i <= j)
While (SortArray(i, col) > X And i < R)
i = i + 1
Wend
While (X > SortArray(j, col) And j > L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
End If
If (L < j) Then Call QuickSort(SortArray, col, L, j, bAscending)
If (i < R) Then Call QuickSort(SortArray, col, i, R, bAscending)
End Sub

 

[RB Smissaert]

For starters try this instead:

Sub aaTesterSort()

Dim vArr()
Dim rng As Range
Dim bAscending As Boolean

Set rng = Range(Cells(1), Cells(13, 2))
vArr = rng.Value
bAscending = False

QuickSort vArr, 2, LBound(vArr), UBound(vArr), bAscending

' vArr now holds a sorted verion of itself
Range(Cells(4), Cells(13, 5)) = vArr

End Sub

Sub QuickSort(SortArray() As Variant, _
col As Long, _
L As Long, _
R As Long, _
bAscending As Boolean)
'
'Originally Posted by Jim Rech 10/20/98 Excel.Programming

' Modifications made by t.w. ogilvy
'Modified to sort on first column of a two dimensional array
'Modified to handle a second dimension greater than 1 (or zero)
'Modified to do Ascending or Descending
Dim i As Long
Dim j As Long
Dim X As Variant
Dim Y As Variant
Dim mm As Long

i = L
j = R
X = SortArray((L + R) / 2, col)

If bAscending Then
While (i <= j)
While (SortArray(i, col) < X And i < R)
i = i + 1
Wend
While (X < SortArray(j, col) And j > L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
Else
While (i <= j)
While (SortArray(i, col) > X And i < R)
i = i + 1
Wend
While (X > SortArray(j, col) And j > L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
End If

If (L < j) Then Call QuickSort(SortArray, col, L, j, bAscending)
If (i < R) Then Call QuickSort(SortArray, col, i, R, bAscending)

End Sub


RBS

 

[Helmut Meukel]

Steven,

where do you get the 5 from?

QuickSort vArr, 5, LBound(vArr, 1), UBound(vArr, 1), bAscending

Obviously vArr is a two-dimenional array. It should contain the cell
values of 6 rows and 2 columns. (I7 to J12)
5 is no valid column number for this array, so you get "Subscript out of
range".
If you want to sort alphabetically this should do it:
QuickSort vArr, LBound(vArr, 2), LBound(vArr, 1), _
UBound(vArr, 1), bAscending
because LBound(vArr, 2) will give you the col number of the first column (I)

Helmut.

 

[Steven]

Perfect

Thank you to both.

Steven

Steven,

where do you get the 5 from?
Obviously vArr is a two-dimenional array. It should contain the cell
values of 6 rows and 2 columns. (I7 to J12)
5 is no valid column number for this array, so you get "Subscript out of
range".
If you want to sort alphabetically this should do it:
QuickSort vArr, LBound(vArr, 2), LBound(vArr, 1), _
UBound(vArr, 1), bAscending
because LBound(vArr, 2) will give you the col number of the first column (I)

Helmut.




.

 

[RB Smissaert]

You will find that declaring all the variables, particularly passing the
array
as a real array: SortArray() As Variant
(note the 2 brackets here) makes the Quicksort a lot faster.

RBS