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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

igold

Well-known Member
Joined
Jul 8, 2014
Messages
3,180
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,176,634
Messages
5,904,143
Members
435,074
Latest member
McKay_S

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
Top