Trying to sort strings in two-dimensional array VBA

F

fat-tony

New Member
Joined
May 12, 2011
Messages
29
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:

Code: 
    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

Code: 
    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
 
Sort by date Sort by votes
kpark91 said:
This will be your sorting code then

I haven't had the change to optimize it.
You might want to take a look at speedSort on the internet.
Click to expand...

Thanks!

Only 2 issues I found:
Firstly is that it treats strings that begin with a lowercase letter as higher value than the uppercase letters.

i.e.
A
B
I
Z
i

the other is a bit odd. when I use both sorts for Col(B) and Col(A) the last 2 entries in the list shouldnt be there as they begin with D and not Z.
It looks like they are sorted by Col(A) only and not Col(B)

Thanks, though this is awesome
Code: 
[COLOR=Navy]Sub [/COLOR]PleaseWork()
    [COLOR=Navy]Dim [/COLOR]tCount [COLOR=Navy]As Long[/COLOR], Row [COLOR=Navy]As Long[/COLOR], Col [COLOR=Navy]As Long[/COLOR], i [COLOR=Navy]As Long[/COLOR], j [COLOR=Navy]As Long[/COLOR]
    [COLOR=Navy]Dim [/COLOR]srcNumRows [COLOR=Navy]As Long[/COLOR], srcNumCols [COLOR=Navy]As Long[/COLOR]
    [COLOR=Navy]Dim [/COLOR]myArray() [COLOR=Navy]As String[/COLOR]

[COLOR=Green]    'get the number of cols in the source sheet[/COLOR]
    srcNumCols = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
[COLOR=Green]    'get the number of rows in the source sheet[/COLOR]
    srcNumRows = Sheets("Sheet1").Cells(Application.Rows.Count, 1).End(xlUp).Row

[COLOR=Green]    'using reDim as I am not using constant expression for the values[/COLOR]
    [COLOR=Navy]ReDim[/COLOR] myArray(1 [COLOR=Navy]To[/COLOR] srcNumRows, 1 [COLOR=Navy]To [/COLOR]srcNumCols)

    [COLOR=Navy]For [/COLOR]Row = 1 [COLOR=Navy]To [/COLOR]srcNumRows    [COLOR=Green]'for each row in the source sheet[/COLOR]
        [COLOR=Navy]For [/COLOR]Col = 1 [COLOR=Navy]To [/COLOR]srcNumCols   [COLOR=Green]'for each col in the source sheet[/COLOR]
[COLOR=Green]            'populate the array with the values from the source sheet[/COLOR]
            myArray(Row, Col) = Sheets("Sheet1").Cells(Row, Col).Value
        [COLOR=Navy]Next [/COLOR]Col
    [COLOR=Navy]Next [/COLOR]Row

[COLOR=DarkGreen]    'Sort Code[/COLOR]
[COLOR=Green] [COLOR=Black]    [COLOR=Navy]For [/COLOR]i = 1 [/COLOR][/COLOR][COLOR=Green][COLOR=Black][COLOR=Navy]To [/COLOR][/COLOR][/COLOR][COLOR=Green][COLOR=Black]srcNumRows - 1
        [/COLOR][/COLOR][COLOR=Green][COLOR=Black][COLOR=Navy]For [/COLOR][/COLOR][/COLOR][COLOR=Green][COLOR=Black]j = i + 1 [/COLOR][/COLOR][COLOR=Green][COLOR=Black][COLOR=Navy]To [/COLOR][/COLOR][/COLOR][COLOR=Green][COLOR=Black]srcNumRows
            [COLOR=Navy]If [/COLOR]myArray(i, 2) > myArray(j, 2) [/COLOR][/COLOR][COLOR=Green][COLOR=Black][COLOR=Navy]Then[/COLOR][/COLOR][/COLOR]
[COLOR=Green][COLOR=Black]                [/COLOR][/COLOR][COLOR=Green][COLOR=Black][COLOR=Navy]For [/COLOR][/COLOR][/COLOR][COLOR=Green][COLOR=Black]k = 1 [/COLOR][/COLOR][COLOR=Green][COLOR=Black][COLOR=Navy]To [/COLOR][/COLOR][/COLOR][COLOR=Green][COLOR=Black]srcNumCols
                    temp = myArray(i, k)
                    myArray(i, k) = myArray(j, k)
                    myArray(j, k) = temp
                [COLOR=Navy]Next [/COLOR]k
            [COLOR=Navy]End If[/COLOR]
            [/COLOR][/COLOR][COLOR=Green][COLOR=Black][COLOR=Navy]If [/COLOR][/COLOR][/COLOR][COLOR=Green][COLOR=Black]myArray(i, 1) > myArray(j, 1) [COLOR=Navy]Then[/COLOR]
                [/COLOR][/COLOR][COLOR=Green][COLOR=Black][COLOR=Navy]For [/COLOR][/COLOR][/COLOR][COLOR=Green][COLOR=Black]k = 1 [COLOR=Navy]To [/COLOR]srcNumCols
                    temp = myArray(i, k)
                    myArray(i, k) = myArray(j, k)
                    myArray(j, k) = temp
                [/COLOR][/COLOR][COLOR=Green][COLOR=Black][COLOR=Navy]Next [/COLOR][/COLOR][/COLOR][COLOR=Green][COLOR=Black]k
            [COLOR=Navy]End If[/COLOR]
        [/COLOR][/COLOR][COLOR=Green][COLOR=Black][COLOR=Navy]Next [/COLOR][/COLOR][/COLOR][COLOR=Green][COLOR=Black]j
    [COLOR=Navy]Next [/COLOR]i[/COLOR][/COLOR]

[COLOR=Green]    'now to iterate through the array and spit out the values in the destination sheet[/COLOR]
    [COLOR=Navy]For [/COLOR]i = [COLOR=Navy]LBound[/COLOR](myArray, 1) [COLOR=Navy]To UBound[/COLOR](myArray, 1)
        [COLOR=Navy]For [/COLOR]j = [COLOR=Navy]LBound[/COLOR](myArray, 2) [COLOR=Navy]To [/COLOR][COLOR=Navy]UBound[/COLOR](myArray, 2)
            Sheets("Sheet2").Cells(i, j).Value = myArray(i, j)
        [COLOR=Navy]Next [/COLOR]j
    [COLOR=Navy]Next [/COLOR]i
[COLOR=Navy]End Sub[/COLOR]
 
Upvote 0

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
it would be easier just to dump it to the sheet and click 'Sort' but this has started to become a battle I dont want the VBA to win.
Click to expand...
The VBA could dump the data to the destination worksheet and sort it in situ by as many columns as you want. The user wouldn't have to click anything other than to start the macro.
 
Upvote 0
fat-tony said:
Thanks!

Only 2 issues I found:
Firstly is that it treats strings that begin with a lowercase letter as higher value than the uppercase letters.

i.e.
A
B
I
Z
i

the other is a bit odd. when I use both sorts for Col(B) and Col(A) the last 2 entries in the list shouldnt be there as they begin with D and not Z.
It looks like they are sorted by Col(A) only and not Col(B)

Thanks, though this is awesome
Click to expand...

:P the reason by lowercase is higher than capitals is due to something called ascii table. When any programming is comparing characters they follow the value of characters which is designated in ASCII table :P
To avoid this, you would use UCase() when you're comparing the text.

as for the sorting of col(A) or col(B). That was how I understood ur problem.
Sort by col(B) THEN by col(A), could you elaborate on ur problem more then? like give examples with 3 x 3 matrix or somethin :P
 
Upvote 0
kpark91 said:
:P the reason by lowercase is higher than capitals is due to something called ascii table. When any programming is comparing characters they follow the value of characters which is designated in ASCII table :P
To avoid this, you would use UCase() when you're comparing the text.
Click to expand...
Yeh :) I thought it would be something along those lines.

kpark91 said:
as for the sorting of col(A) or col(B). That was how I understood ur problem.
Sort by col(B) THEN by col(A), could you elaborate on ur problem more then? like give examples with 3 x 3 matrix or somethin :P
Click to expand...

I tried playing with the macros just to give a better indication of what I meant. Sorry, I should have done that earlier to avoid any confusion.

Original Data:
Rich (BB code): 
       Col(A)         Col(B)            Col(C)           Col(D)
R1    B Row 1      Harry Row 1     Foo Row 1      Bar Row 1
R2    A Row 2      **** Row 2      Foo Row 2      Bar Row 2
R3    C Row 3      Tom Row 3      Foo Row 3      Bar Row 3
R4    D Row 4      **** Row 4      Foo Row 4      Bar Row 4

Ideal Result, but not the ideal way to accomplish it:
Rich (BB code): 
       Col(A)        Col(B)             Col(C)           Col(D)
R1    A Row 2      **** Row 2      Foo Row 2      Bar Row 2
R2    D Row 4      **** Row 4      Foo Row 4      Bar Row 4
R3    B Row 1      Harry Row 1     Foo Row 1      Bar Row 1
R4    C Row 3      Tom Row 3      Foo Row 3      Bar Row 3

Created Using:
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B1:B4"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A4"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:D4")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Using Excel to sort the data in the sheets means that I cannot use the sorted array later unless to dump the data to the sheet and then create an array using the new sheets data.

shg said:
The VBA could dump the data to the destination worksheet and sort it in situ by as many columns as you want. The user wouldn't have to click anything other than to start the macro.
Click to expand...

In an ideal world the array itself would be sorted and then would give more flexibility for further use without having to dump to a sheet. But I agree, this would have been the quicker and simpler solution.

Using the last version of the sort code gives the following result:
Rich (BB code): 
        Col(A)        Col(B)            Col(C)           Col(D)
R1    A Row 2      **** Row 2      Foo Row 2      Bar Row 2
R2    B Row 1      Harry Row 1     Foo Row 1      Bar Row 1
R3    C Row 3      Tom Row 3      Foo Row 3      Bar Row 3
R4    D Row 4      **** Row 4      Foo Row 4      Bar Row 4

Created Using:
     For i = 1 To srcNumRows - 1
        For j = i + 1 To srcNumRows
            If myArray(i, 2) > myArray(j, 2) Then
                 For k = 1 To srcNumCols
                    temp = myArray(i, k)
                    myArray(i, k) = myArray(j, k)
                    myArray(j, k) = temp
                Next k
            End If
            If myArray(i, 1) > myArray(j, 1) Then
                For k = 1 To srcNumCols
                    temp = myArray(i, k)
                    myArray(i, k) = myArray(j, k)
                    myArray(j, k) = temp
                Next k
            End If
        Next j
    Next i

Thanks.
 
Upvote 0
try
Rich (BB code): 
     For i = 1 To srcNumRows - 1
        For j = i + 1 To srcNumRows
            If myArray(i, 2) > myArray(j, 2) Then
                 For k = 1 To srcNumCols
                    temp = myArray(i, k)
                    myArray(i, k) = myArray(j, k)
                    myArray(j, k) = temp
                Next k
            End If
        Next j
    Next i
 
Upvote 0
Hi, Thanks for still helping.
kpark91 said:
try
Rich (BB code): 
    For i = 1 To srcNumRows - 1
        For j = i + 1 To srcNumRows
            If myArray(i, 2) > myArray(j, 2) Then
                 For k = 1 To srcNumCols
                    temp = myArray(i, k)
                    myArray(i, k) = myArray(j, k)
                    myArray(j, k) = temp
                Next k
            End If
        Next j
    Next i
Click to expand...

Doesnt do the 2 layer sort, but works when sorting just 1 column again.

Code: 
Original:
      Col(A)      Col(B)         Col(C)        Col(D)
R1    B Row 1     Harry Row 1    Foo Row 1     Bar Row 1
R2    D Row 2     **** Row 2     Foo Row 2     Bar Row 2
R3    C Row 3     Tom Row 3      Foo Row 3     Bar Row 3
R4    A Row 4     **** Row 4     Foo Row 4     Bar Row 4

Using the Sort:
      Col(A)      Col(B)         Col(C)        Col(D)
R1    D Row 2     **** Row 2     Foo Row 2     Bar Row 2
R2    A Row 4     **** Row 4     Foo Row 4     Bar Row 4
R3    B Row 1     Harry Row 1    Foo Row 1     Bar Row 1
R4    C Row 3     Tom Row 3      Foo Row 3     Bar Row 3
 
Last edited:
Upvote 0
I have no idea what you mean anymore..
Your original data has changed as well

Col(A) Col(B) Col(C) Col(D)
R1 B Row 1 Harry Row 1 Foo Row 1 Bar Row 1
R2 A Row 2 **** Row 2 Foo Row 2 Bar Row 2
R3 C Row 3 Tom Row 3 Foo Row 3 Bar Row 3
R4 D Row 4 **** Row 4 Foo Row 4 Bar Row 4
Click to expand...

Original:
Col(A) Col(B) Col(C) Col(D)
R1 B Row 1 Harry Row 1 Foo Row 1 Bar Row 1
R2 D Row 2 **** Row 2 Foo Row 2 Bar Row 2
R3 C Row 3 Tom Row 3 Foo Row 3 Bar Row 3
R4 A Row 4 **** Row 4 Foo Row 4 Bar Row 4
Click to expand...

So, there's no way they could look the same after the sort since they are two different data.
If you want B column to be sorted, just change
Rich (BB code): 
If myArray(i, 1) > myArray(j, 1) Then
to
Rich (BB code): 
If myArray(i, 2) > myArray(j, 2) Then



 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Is it possible to sort table column values and display the header value in the next set of table columns
Replies
7
Views
96
Peter_SSs
Peter_SSs
F
  • Question Question
Read an array from a worksheet cell
Replies
4
Views
504
FryGirl
F
F
  • Question Question
Storing data in 2D array
Replies
5
Views
883
Formula11
F
Smerdis13
  • Question Question
VBA to Get Output by check relations from Input
Replies
2
Views
303
Smerdis13
Smerdis13
M
  • Question Question
Sorting array code in VBA (Excel) not working correctly
Replies
1
Views
960
igold
igold

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top