Trying to sort strings in two-dimensional array VBA

[fat-tony]

Hi Guys,

I have been trying to sort a two-dimensional array in excel using VBA.
the values of the array are all strings not numbers, which has been causing my head to explode.

I have an excel sheet that contains data 4 rows by 4 columns.
I then want to put that data in to an array so I can use it.

This is the first time I have been playing with 2D Arrays in VBA. From what I gathered this is how it should look:

    Dim MyArray(1 To 4, 1 To 4)
    MyArray(1, 1) = "A col1 row1"
    MyArray(1, 2) = "C col1  row2"
    MyArray(1, 3) = "D col1  row3"
    MyArray(1, 4) = "B col1  row4"
    MyArray(2, 1) = "D col2  row1 - sort this col"
    MyArray(2, 2) = "B col2   row2 - sort this col"
    MyArray(2, 3) = "A col2   row3 - sort this col"
    MyArray(2, 4) = "C col2   row4 - sort this col"
    MyArray(3, 1) = "A col3  row1"
    MyArray(3, 2) = "D col3 row2"
    MyArray(3, 3) = "B col3 row3"
    MyArray(3, 4) = "C col3 row4"
    MyArray(4, 1) = "D col4 row1"
    MyArray(4, 2) = "C col4 row2"
    MyArray(4, 3) = "B col4 row3"
    MyArray(4, 4) = "A col4 row4"

so going by this principal i tried to sort the array by the 2nd column. I tried various examples from other sites but they all say that "subscript out of range"

can anyone please let me know if I am going about this completely the wrong way. Any help greatly appreciated. Thanks

    Dim MyArray(1 To 4, 1 To 4)
    Dim i As Integer, j As Integer

    MyArray(1, 1) = "A col1 row1"
     MyArray(1, 2) = "C col1  row2"
     MyArray(1, 3) = "D col1  row3"
     MyArray(1, 4) = "B col1  row4"
     MyArray(2, 1) = "D col2  row1 - sort this col"
     MyArray(2, 2) = "B col2   row2 - sort this col"
     MyArray(2, 3) = "A col2   row3 - sort this col"
     MyArray(2, 4) = "C col2   row4 - sort this col"
     MyArray(3, 1) = "A col3  row1"
     MyArray(3, 2) = "D col3 row2"
     MyArray(3, 3) = "B col3 row3"
     MyArray(3, 4) = "C col3 row4"
     MyArray(4, 1) = "D col4 row1"
     MyArray(4, 2) = "C col4 row2"
     MyArray(4, 3) = "B col4 row3"
     MyArray(4, 4) = "A col4 row4"
 
 SortColumm1 = 1
 SortColumn2 = 3
 For i = LBound(MyArray, 1) To UBound(MyArray, 1) - 1
      For j = LBound(MyArray, 1) To UBound(MyArray, 1) - 1
          Condition1 = MyArray(j, SortColumn1) > MyArray(j + 1, SortColumn1)
          Condition2 = MyArray(j, SortColumn1) = MyArray(j + 1, SortColumn1) And _
                     MyArray(j, SortColumn2) > MyArray(j + 1, SortColumn2)

          If Condition1 Or Condition2 Then
               For y = LBound(MyArray, 2) To UBound(MyArray, 2)
                   t = MyArray(j, y)
                   MyArray(j, y) = MyArray(j + 1, y)
                   MyArray(j + 1, y) = t
                   Next y
               End If
          Next
      Next

 

[kpark91]

Are you trying to sort by the column? or by the row?

A B C D
A B C D

or

A A
B B
C C
D D

 

[kpark91]

I've switched your row and column because I was just bothered by it. Altho it doesn't make a huge difference.

Sub SortTwoDimensionalArray()
       
    Dim MyArray(1 To 4, 1 To 4)
    Dim i As Long, j As Long, temp As String
    MyArray(1, 1) = "A r1c1"
    MyArray(1, 2) = "C r1c2"
    MyArray(1, 3) = "D r1c3"
    MyArray(1, 4) = "B r1c4"
    MyArray(2, 1) = "D r2c1"
    MyArray(2, 2) = "B r2c2"
    MyArray(2, 3) = "A r2c3"
    MyArray(2, 4) = "C r2c4"
    MyArray(3, 1) = "A r3c1"
    MyArray(3, 2) = "D r3c2"
    MyArray(3, 3) = "B r3c3"
    MyArray(3, 4) = "C r3c4"
    MyArray(4, 1) = "D r4c1"
    MyArray(4, 2) = "C r4c2"
    MyArray(4, 3) = "B r4c3"
    MyArray(4, 4) = "A r4c4"
    
    'Sort column 2 (slowest sorting algorithm) increasing downwards
    For i = 1 To 4
        For j = 1 To 4
            If MyArray(i, 2) <= MyArray(j, 2) Then
                temp = MyArray(i, 2)
                MyArray(i, 2) = MyArray(j, 2)
                MyArray(j, 2) = temp
            End If
        Next j
    Next i
    
    For i = 1 To 4
        For j = 1 To 4
            Cells(i, j).Value = MyArray(i, j)
        Next j
    Next i
End Sub

 

[MickG]

Try this:-
Set you range (Range("A1:D4") in as a Variant Array.
Then sort array by column "B"
Then place sorted data Back on sheet, starting "A10".

[COLOR=navy]Sub[/COLOR] MG12May34
[COLOR=navy]Dim[/COLOR] Ray [COLOR=navy]As[/COLOR] Variant
Ray = Range("A1:D4").value
[COLOR=navy]Dim[/COLOR] Temp1 [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Temp2 [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Temp3 [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Temp4 [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Rw [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
'[COLOR=green][B]sort By column "B"[/B][/COLOR]
[COLOR=navy]For[/COLOR] Rw = 1 To UBound(Ray)
    [COLOR=navy]For[/COLOR] Ac = Rw To UBound(Ray)
        [COLOR=navy]If[/COLOR] Ray(Ac, 2) < Ray(Rw, 2) [COLOR=navy]Then[/COLOR]
            Temp1 = Ray(Rw, 1)
            Temp2 = Ray(Rw, 2)
            Temp3 = Ray(Rw, 3)
            Temp4 = Ray(Rw, 4)
                Ray(Rw, 1) = Ray(Ac, 1)
                Ray(Rw, 2) = Ray(Ac, 2)
                Ray(Rw, 3) = Ray(Ac, 3)
                Ray(Rw, 4) = Ray(Ac, 4)
                   Ray(Ac, 1) = Temp1
                   Ray(Ac, 2) = Temp2
                   Ray(Ac, 3) = Temp3
                   Ray(Ac, 4) = Temp4
        [COLOR=navy]End[/COLOR] If
  [COLOR=navy]Next[/COLOR] Ac
[COLOR=navy]Next[/COLOR] Rw
Range("A10").Resize(UBound(Ray, 1), UBound(Ray, 2)) = Ray
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

[COLOR=royalblue][B]Row No [/B][/COLOR][COLOR=royalblue][B]Col(A) [/B][/COLOR][COLOR=royalblue][B]Col(B) [/B][/COLOR][COLOR=royalblue][B]Col(C) [/B][/COLOR][COLOR=royalblue][B]Col(D) [/B][/COLOR]
1.      a       e       f       k      
2.      b       d       g       l      
3.      c       c       h       m      
4.      d       b       j       n      
5.                                     
6.                                     
7.                                     
8.                                     
9.                                     
10.     d       b       j       n      
11.     c       c       h       m      
12.     b       d       g       l      
13.     a       e       f       k

Regards Mick

 

[fat-tony]

Are you trying to sort by the column? or by the row?

A B C D
A B C D

or

A A
B B
C C
D D

Sorry in my haste I completely forgot how to describe the requirement. I will have to blame it on my brain having melt due to trolling through google for so long.

Unsorted:
Row 1
MyArray(1, 1) = "A col1 row1"
MyArray(2, 1) = "D col2 row1 - sort this col"
MyArray(3, 1) = "A col3 row1"
MyArray(4, 1) = "D col4 row1"
Row 2
MyArray(1, 3) = "D col1 row3"
MyArray(2, 3) = "A col2 row3 - sort this col"
MyArray(3, 3) = "B col3 row3"
MyArray(4, 3) = "B col4 row3"
Row 3
MyArray(1, 2) = "C col1 row2"
MyArray(2, 2) = "B col2 row2 - sort this col"
MyArray(3, 2) = "D col3 row2"
MyArray(4, 2) = "C col4 row2"
Row 4
MyArray(1, 4) = "B col1 row4"
MyArray(2, 4) = "C col2 row4 - sort this col"
MyArray(3, 4) = "C col3 row4"
MyArray(4, 4) = "A col4 row4"

Using MickG's nicely formatted table it would look like:

unsorted    
Row No Col(A) Col(B) Col(C) Col(D) 
1.      a       d       a       d      
2.      c       b       d       c      
3.      d       a       b       b      
4.      b       c       c       a      

sorted using Col(B)
Row No Col(A) Col(B) Col(C) Col(D)    
1.     d       a       b       b      
2.     c       b       d       c      
3.     b       c       c       a      
4.     a       d       a       d

 

[fat-tony]

I've switched your row and column because I was just bothered by it. Altho it doesn't make a huge difference.

.....

Try this:-
Set you range (Range("A1:D4") in as a Variant Array.
Then sort array by column "B"
Then place sorted data Back on sheet, starting "A10".
.........
Regards Mick

Thanks Guys. You both rock.

regarding the cols being first followed by the rows, the tutorials I found on 2d arrays had cols first and in the true case of monkey-see monkey-do I used it. I agree. was an awkward way of formatting it.

 

[kpark91]

in the true case of monkey-see monkey-do I used it.

LOLLLL
np :P<!-- / message -->

 

[fat-tony]

Hi Guys,

Both codes work great.

for i = 1 to infinity
    msgbox "Thank You"
next i

I hope that my next cheeky question does not make me outstay my welcome on my first day on the forum:

I found that in the table I have some duplicate strings in Col(B) so need to add another layer to the sort.

Could these scripts be used to add more levels to the sort? So like:
first sort by Col(B) and then by Col(A)?

I tried nesting the loop but it just laughed at me.



Thanks again

 

[fat-tony]

LOLLLL
np :P<!-- / message -->

Found the reason why Cols was used first and the second was the rows
apparently "You can only ReDim the outer bound of Multidemensional Arrays".

I found online some code to get around this, but the easy way does seem to stick with cols first.

So that clears up that mystery.

 

[kpark91]

Ahh well. I see why you put 'columns' at first then :P
Could I see the code that is able to redim all the dimensions of an array please if you don't mind?

As for your sorting by colB then colA..
I just read it right now and in what ways do you want them sorted?

like just column A and column B independent from the rows the elements of column space are in, right?