Sorting 2D Array
 

Hi folks. I have been sorting a VBA Array using a bubble sort. I
works fine with when my VBA array is 1-D, but when I change to 2-D it
get a "Subscript out of range" Error.


I have an array called UnitOfferArray

ReDim UnitOfferArray(1 To NumberofRows, 1 To 4)

For X = 1 to 10

Next X


BubbleSort UnitOfferArray

Function BubbleSort(List As Variant)
' Sorts an array using bubble sort algorithm
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp As Integer

First = LBound(List)
Last = UBound(List)
For i = 1 To Last - 1
For j = i + 1 To Last
If List(i) List(j) Then
Temp = List(j)
List(j) = List(i)
List(i) = Temp
End If
Next j
Application.StatusBar = "Sorting " & Round(i / Last * 100, 0)
"%"
Next i

End Functio

--
Message posted from http://www.ExcelForum.com

Sorting 2D Array
 

Sorry, I hit send to early


Hi folks. I have been sorting a VBA Array using a bubble sort. It work
fine with when my VBA array is 1-D, but when I change to 2-D it I get
"Subscript out of range" Error. Is there something obvious I a
forgetting?




ReDim UnitOfferArray(1 To 10, 1 To 4)

For X = 1 to 10
I load the data into the array within this loop
Next X


BubbleSort UnitOfferArray

Function BubbleSort(List As Variant)
' Sorts an array using bubble sort algorithm
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp As Integer

First = LBound(List)
Last = UBound(List)
For i = 1 To Last - 1
For j = i + 1 To Last
If List(i) List(j) Then
Temp = List(j)
List(j) = List(i)
List(i) = Temp
End If
Next j
Application.StatusBar = "Sorting " & Round(i / Last * 100, 0) & "%"
Next i

End Functio

--
Message posted from http://www.ExcelForum.com

Sorting 2D Array
 

ExcelMonkey

The Bubble Sort is constructed to do a sort on a single dimention
array.

when looking at the List array it needs to know all dimentions

your code has

Temp = List(j)

which is ok for 1 dimesion array

for 2 dimention array it needs something like

dim Temp(2)


Temp(1) = List(j, 1)
Temp(2) = List(j, 2)

List(j,1) = List(i,1)
List(j,2) = List(i,2)
List(i,1) = Temp(1)
List(i,2) = Temp(2)


---
Message posted from http://www.ExcelForum.com/

Sorting 2D Array
 

Your forgetting that a 2D array has two dimensions.

Assuming that you are sorting on the leftmost column

BubbleSort2D UnitOfferArray

Function BubbleSort2D(List As Variant)
' Sorts an array using bubble sort algorithm
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp As Integer

First = LBound(List,1)
Last = UBound(List,1)
For i = 1 To Last - 1
For j = i + 1 To Last
If List(i,1) List(j,1) Then
for k = 1 to 4
Temp = List(j,k)
List(j,k) = List(i,k)
List(i,k) = Temp
Next k
End If
Next j
Application.StatusBar = "Sorting " & Round(i / Last * 100, 0) & "%"
Next i

End Function

--
Regards,
Tom Ogilvy


ExcelMonkey wrote in message
...
Sorry, I hit send to early


Hi folks. I have been sorting a VBA Array using a bubble sort. It works
fine with when my VBA array is 1-D, but when I change to 2-D it I get a
"Subscript out of range" Error. Is there something obvious I am
forgetting?




ReDim UnitOfferArray(1 To 10, 1 To 4)

For X = 1 to 10
I load the data into the array within this loop
Next X


BubbleSort UnitOfferArray

Function BubbleSort(List As Variant)
' Sorts an array using bubble sort algorithm
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp As Integer

First = LBound(List)
Last = UBound(List)
For i = 1 To Last - 1
For j = i + 1 To Last
If List(i) List(j) Then
Temp = List(j)
List(j) = List(i)
List(i) = Temp
End If
Next j
Application.StatusBar = "Sorting " & Round(i / Last * 100, 0) & "%"
Next i

End Function


---
Message posted from http://www.ExcelForum.com/

Sorting 2D Array
 

Perhaps I should be more specific. I have a 2-D VBA array. How do I
sort it AND define which column element I want to sort it by?


---
Message posted from http://www.ExcelForum.com/

Sorting 2D Array
 

Just to clarify.
See my post for a method to do it without have more than one temp variable.
Even my method could be generalized so a second dimension of 1 to 4 is not
assumed.

--
Regards,
Tom Ogilvy

mudraker wrote in message
...
ExcelMonkey

The Bubble Sort is constructed to do a sort on a single dimention
array.

when looking at the List array it needs to know all dimentions

your code has

Temp = List(j)

which is ok for 1 dimesion array

for 2 dimention array it needs something like

dim Temp(2)


Temp(1) = List(j, 1)
Temp(2) = List(j, 2)

List(j,1) = List(i,1)
List(j,2) = List(i,2)
List(i,1) = Temp(1)
List(i,2) = Temp(2)


---
Message posted from http://www.ExcelForum.com/

Sorting 2D Array
 

So Tom, If I am sorting using column 4 what change do I make to th
code

--
Message posted from http://www.ExcelForum.com

Sorting 2D Array
 

And furthermore Tom, If I want to start experimenting with other sor
functions for speed, do you know of any code on-line which will allo
me to do this by selecting the column element as I outlined above

--
Message posted from http://www.ExcelForum.com

Sorting 2D Array
 

Which posting are you talking about Tom

--
Message posted from http://www.ExcelForum.com

Sorting 2D Array
 

Here is a generalized version that allows you to specify the column and can
handle whatever the second dimension is.

Sub Doit()
Dim v as Variant
Dim rng as Range
Set rng = Range("A1").CurrentRegion.Columns(1)
v = rng.Value
BubbleSort2D v, 3 '<== sorts on 3rd column
rng.Value = v
End Sub

Function BubbleSort2D(List As Variant, col As Long)
' Sorts an array using bubble sort algorithm
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp As Integer

First = LBound(List, 1)
Last = UBound(List, 1)
For i = 1 To Last - 1
For j = i + 1 To Last
If List(i, col) List(j, col) Then
For k = 1 To UBound(List, 2)
Temp = List(j, k)
List(j, k) = List(i, k)
List(i, k) = Temp
Next k
End If
Next j
'Application.StatusBar = "Sorting " & Round(i / Last * 100, 0) & "%"
Next i

End Function

--
Regards,
Tom Ogilvy

Tom Ogilvy wrote in message
...
Your forgetting that a 2D array has two dimensions.

Assuming that you are sorting on the leftmost column

BubbleSort2D UnitOfferArray

Function BubbleSort2D(List As Variant)
' Sorts an array using bubble sort algorithm
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp As Integer

First = LBound(List,1)
Last = UBound(List,1)
For i = 1 To Last - 1
For j = i + 1 To Last
If List(i,1) List(j,1) Then
for k = 1 to 4
Temp = List(j,k)
List(j,k) = List(i,k)
List(i,k) = Temp
Next k
End If
Next j
Application.StatusBar = "Sorting " & Round(i / Last * 100, 0) & "%"
Next i

End Function

--
Regards,
Tom Ogilvy


ExcelMonkey wrote in message
...
Sorry, I hit send to early


Hi folks. I have been sorting a VBA Array using a bubble sort. It works
fine with when my VBA array is 1-D, but when I change to 2-D it I get a
"Subscript out of range" Error. Is there something obvious I am
forgetting?




ReDim UnitOfferArray(1 To 10, 1 To 4)

For X = 1 to 10
I load the data into the array within this loop
Next X


BubbleSort UnitOfferArray

Function BubbleSort(List As Variant)
' Sorts an array using bubble sort algorithm
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp As Integer

First = LBound(List)
Last = UBound(List)
For i = 1 To Last - 1
For j = i + 1 To Last
If List(i) List(j) Then
Temp = List(j)
List(j) = List(i)
List(i) = Temp
End If
Next j
Application.StatusBar = "Sorting " & Round(i / Last * 100, 0) & "%"
Next i

End Function


---
Message posted from http://www.ExcelForum.com/

Sorting 2D Array
 

See my revised code. (also tested)

--
Regards,
Tom Ogilvy


ExcelMonkey wrote in message
...
So Tom, If I am sorting using column 4 what change do I make to the
code?


---
Message posted from http://www.ExcelForum.com/

Sorting 2D Array
 

When I use this I get a type mismatch error on Temp = List(j, k). I hav
assumed it sorts on Column 4.

Main Sub ()
Dim UnitOfferArray() As Variant
ReDim UnitOfferArray(1 to 13, 1 To 4)

For Next Loop which fills array

BubbleSort2D UnitOfferArray, 4

End Sub

Function BubbleSort2D(List As Variant, col As Long)
' Sorts an array using bubble sort algorithm
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp As Integer

First = LBound(List, 1)
Last = UBound(List, 1)
For i = 1 To Last - 1
For j = i + 1 To Last
If List(i, col) List(j, col) Then
For k = 1 To UBound(List, 2)
Temp = List(j, k)
List(j, k) = List(i, k)
List(i, k) = Temp
Next k
End If
Next j
'Application.StatusBar = "Sorting " & Round(i / Last * 100, 0) & "%"
Next i

End Functio

--
Message posted from http://www.ExcelForum.com

Sorting 2D Array
 

OK I am confused. I can't get this to work. I have reworked it to
simplify. I have a 2-D array. I fill the array with a Rnd Function.
I then want to sort the array by the 4th element of the 2nd dimension.
For error checking I paste the unsorted array to a cell range called
"PasetCell1"(A1:D10), and then I paste the sorted verion of the array
to a range called "PasetCell2"(F1:I10). You can see from the results
that I am clearly not sorting this thing by the 4th element of the 2nd
dimension.

Sorry to drag this thread out.





Sub Thing()
Dim RandArray() As Variant

Range("PasetCell1").Clear
Range("PasetCell2").Clear

ReDim RandArray(1 To 10, 1 To 4)

For X = 1 To 10
For Y = 1 To 4
RandArray(X, Y) = Rnd()
Next Y
Next X

MsgBox ("Maximum of 2D Element is" &
Application.WorksheetFunction.Max(RandArray))

'Paste unsorted version to excel A1:D10
Range("PasetCell1") = RandArray

BubbleSort2D RandArray, 4

'Paste sorted version to excel F1:I10
Range("PasetCell2") = RandArray
End Sub

Function BubbleSort2D(RandArray As Variant, col As Long)
' Sorts an array using bubble sort algorithm
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp As Integer

First = LBound(RandArray, 1)
Last = UBound(RandArray, 1)
For i = 1 To Last - 1
For j = i + 1 To Last
If RandArray(i, col) RandArray(j, col) Then
For k = 1 To UBound(RandArray, 2)
Temp = RandArray(j, k)
RandArray(j, k) = RandArray(i, k)
RandArray(i, k) = Temp
Next k
End If
Next j
'Application.StatusBar = "Sorting " & Round(i / Last * 100, 0) & "%"
Next i

End Function


---
Message posted from http://www.ExcelForum.com/

Sorting 2D Array
 

In the bubble sort, change the declaration of Temp to Variant

Dim Temp as Variant.

Then you shouldn't get a type mismatch.

I tested it with both an array of numbers and an array of letters:

Main sub for Number:

Sub Main()
Dim UnitOfferArray() As Variant
ReDim UnitOfferArray(1 To 13, 1 To 4)

For i = 1 To 13
For j = 1 To 4
UnitOfferArray(i, j) = Int(Rnd() * 1000 + 1)
Next
Next
Range("A1").Resize(13, 4).Value = UnitOfferArray

BubbleSort2D UnitOfferArray, 4

Range("F1").Resize(13, 4).Value = UnitOfferArray

End Sub

------------------------

Main Sub for Letters:

Sub Main1()
Dim UnitOfferArray() As Variant
ReDim UnitOfferArray(1 To 13, 1 To 4)

For i = 1 To 13
For j = 1 To 4
UnitOfferArray(i, j) = Chr(Int(Rnd() * 26 + 65))
Next
Next
Range("A1").Resize(13, 4).Value = UnitOfferArray

BubbleSort2D UnitOfferArray, 4

Range("F1").Resize(13, 4).Value = UnitOfferArray

End Sub

------------------

Function BubbleSort2D(List As Variant, col As Long)
' Sorts an array using bubble sort algorithm
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp As Variant

First = LBound(List, 1)
Last = UBound(List, 1)
For i = 1 To Last - 1
For j = i + 1 To Last
If List(i, col) List(j, col) Then
For k = 1 To UBound(List, 2)
Temp = List(j, k)
List(j, k) = List(i, k)
List(i, k) = Temp
Next k
End If
Next j
'Application.StatusBar = "Sorting " & Round(i / Last * 100, 0) & "%"
Next i

End Function

--
Regards,
Tom Ogilvy

"ExcelMonkey " wrote in message
...
OK I am confused. I can't get this to work. I have reworked it to
simplify. I have a 2-D array. I fill the array with a Rnd Function.
I then want to sort the array by the 4th element of the 2nd dimension.
For error checking I paste the unsorted array to a cell range called
"PasetCell1"(A1:D10), and then I paste the sorted verion of the array
to a range called "PasetCell2"(F1:I10). You can see from the results
that I am clearly not sorting this thing by the 4th element of the 2nd
dimension.

Sorry to drag this thread out.





Sub Thing()
Dim RandArray() As Variant

Range("PasetCell1").Clear
Range("PasetCell2").Clear

ReDim RandArray(1 To 10, 1 To 4)

For X = 1 To 10
For Y = 1 To 4
RandArray(X, Y) = Rnd()
Next Y
Next X

MsgBox ("Maximum of 2D Element is" &
Application.WorksheetFunction.Max(RandArray))

'Paste unsorted version to excel A1:D10
Range("PasetCell1") = RandArray

BubbleSort2D RandArray, 4

'Paste sorted version to excel F1:I10
Range("PasetCell2") = RandArray
End Sub

Function BubbleSort2D(RandArray As Variant, col As Long)
' Sorts an array using bubble sort algorithm
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp As Integer

First = LBound(RandArray, 1)
Last = UBound(RandArray, 1)
For i = 1 To Last - 1
For j = i + 1 To Last
If RandArray(i, col) RandArray(j, col) Then
For k = 1 To UBound(RandArray, 2)
Temp = RandArray(j, k)
RandArray(j, k) = RandArray(i, k)
RandArray(i, k) = Temp
Next k
End If
Next j
'Application.StatusBar = "Sorting " & Round(i / Last * 100, 0) & "%"
Next i

End Function


---
Message posted from http://www.ExcelForum.com/

Sorting 2D Array
 

Monkey See Monkey Do!

Works fine Tom. Thank-you. Guys like you allow guys like me to burn
the midnight oil.

Cheers!


---
Message posted from http://www.ExcelForum.com/