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

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



"Steven" <(E-Mail Removed)> wrote in message
news:80FD1164-507B-4E11-BD1D-(E-Mail Removed)...
>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
>
>

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" <(E-Mail Removed)> schrieb im Newsbeitrag
news:80FD1164-507B-4E11-BD1D-(E-Mail Removed)...
>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
>
>

Perfect

Thank you to both.

Steven

"Helmut Meukel" wrote:

> 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" <(E-Mail Removed)> schrieb im Newsbeitrag
> news:80FD1164-507B-4E11-BD1D-(E-Mail Removed)...
> >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
> >
> >
>
>
> .
>

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


"Steven" <(E-Mail Removed)> wrote in message
news:BB129AD2-25BC-4F51-9E39-(E-Mail Removed)...
>
> Perfect
>
> Thank you to both.
>
> Steven
>
> "Helmut Meukel" wrote:
>
>> 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" <(E-Mail Removed)> schrieb im Newsbeitrag
>> news:80FD1164-507B-4E11-BD1D-(E-Mail Removed)...
>> >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
>> >
>> >
>>
>>
>> .
>>