Sorting array code in VBA (Excel) not working correctly

mzanlongo

New Member
Joined
Jun 27, 2022
Messages
1
Office Version
  1. 2010
Platform
  1. Windows
I wrote the following code which creates a 3 by 3 table and then sorts the columns from smallest to biggest depending on the values in row 1 (second row). For some reason, with the values below I get to a point where the code does "If( 2>1) Then" but the code then says its FALSE and skips, I have no idea why. Can anyone see what the issue may be?

VBA Code:
Sub SortButton()

Dim MyArray As Variant
ReDim MyArray(0 To 2, 0 To 2) As Variant
Dim x As Integer, y As Integer, z As Integer
Dim str0 As String, str1 As String, str2 As String

MyArray(0, 0) = MC
MyArray(0, 1) = Branch1
MyArray(0, 2) = Branch2
MyArray(1, 0) = 1
MyArray(1, 1) = 2
MyArray(1, 2) = 0

For x = LBound(MyArray, 2) To UBound(MyArray, 2) - 1
    Sheets("Sheet1").Range("A9") = x
    For y = (x + 1) To UBound(MyArray, 2)
        Sheets("Sheet1").Range("A9") = x
        Sheets("Sheet1").Range("A10") = y
        Sheets("Sheet1").Range("A11") = MyArray(1, x)
        Sheets("Sheet1").Range("C11") = MyArray(1, y)
      
        If MyArray(1, x) > MyArray(1, y) Then
            str0 = MyArray(0, x)
            str1 = MyArray(1, x)
              
            MyArray(0, x) = MyArray(0, y)
            MyArray(1, x) = MyArray(1, y)
              
            MyArray(0, y) = str0
            MyArray(1, y) = str1
              
            str0 = ""
            str1 = ""

        End If     
    Next y
Next x

Sheets("Sheet1").Range("A1") = MyArray(0, 0)
Sheets("Sheet1").Range("B1") = MyArray(0, 1)
Sheets("Sheet1").Range("C1") = MyArray(0, 2)
Sheets("Sheet1").Range("A2") = MyArray(1, 0)
Sheets("Sheet1").Range("B2") = MyArray(1, 1)
Sheets("Sheet1").Range("C2") = MyArray(1, 2)

End Sub


First it does 1>2 which returns false, then 1>0 which return true and swaps the columns over, then it does 2>1 but returns FALSE, I can not find out why.

Thanks in advance!
 
Last edited by a moderator:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Welcome to the MrExcel Forum.

In your code you have declared str1 as string
Dim str0 As String, str1 As String, str2 As String
you then set MyArray(1,y) = str1

MyArray(1, y) = str1
That sets MyArray(1,y) as a Variant/String as opposed to a Variant/Integer so this statement

If MyArray(1, x) > MyArray(1, y) Then
is really saying if 2 > "1" which is where the problem lies (notice the quotation marks around the 1).

I hope this makes sense. If you step through your code using F8 and watch the MyArray values in the Locals window you will see what I mean.
 
Upvote 0

Forum statistics

Threads
1,218,838
Messages
6,144,768
Members
450,567
Latest member
Mplz

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