Alphabetize a list

jjpski

New Member
Joined
Jan 4, 2011
Messages
29
I have this code to alphabetize list values then add them back. I keep getting an error Run-time error 9 Subscript out of range. On line If Ray(j) < Ray(i) And Ray(j) <> "" . I have this working in many other parts of this project but the list is a range. Ray = Range("N2:N" & LastRow).Value




Dim Ray, i As Integer, j As Integer, Temp As String
Ray = lbOFFICE.List
lbOFFICE.Clear
'MsgBox lbOFFICE.List
For i = 1 To UBound(Ray) - 1
For j = i To UBound(Ray)
If Ray(j) < Ray(i) And Ray(j) <> "" Then
Temp = Ray(i)
Ray(i) = Ray(j)
Ray(j) = Temp
End If
Next j
Next i
lbOFFICE.List = Ray
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

hotabae

Board Regular
Joined
Apr 18, 2018
Messages
167
I know this isn't a straightforward solution, but when I have issues with loops, the first thing I do is determine WHERE the loop is failing.

I'd recommend inserting a line like this and then watching the "Immediate" code window to figure out which I // J value is causing the problem.

Code:
For i = 1 To UBound(Ray) - 1
For j = i To UBound(Ray)
[COLOR=#ff0000]Debug.Print("i: " & i & " j: " & j)[/COLOR]
If Ray(j) < Ray(i) And Ray(j) <> "" Then
Temp = Ray(i)
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,437
Office Version
  1. 365
Platform
  1. Windows
try
Code:
Ray = Application.Transpose(lbOFFICE.List)
 

jjpski

New Member
Joined
Jan 4, 2011
Messages
29

ADVERTISEMENT

This is all the output.

i: 1 j: 1

:confused:

I know this isn't a straightforward solution, but when I have issues with loops, the first thing I do is determine WHERE the loop is failing.

I'd recommend inserting a line like this and then watching the "Immediate" code window to figure out which I // J value is causing the problem.

Code:
For i = 1 To UBound(Ray) - 1
For j = i To UBound(Ray)
[COLOR=#ff0000]Debug.Print("i: " & i & " j: " & j)[/COLOR]
If Ray(j) < Ray(i) And Ray(j) <> "" Then
Temp = Ray(i)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,437
Office Version
  1. 365
Platform
  1. Windows
What is lbOFFICE & how are you populating it?
Also how are you calling the code you've supplied?
 

jjpski

New Member
Joined
Jan 4, 2011
Messages
29

ADVERTISEMENT

Code:
Private Sub cbSTATE_Change()
Dim rngSTATE As Range
Dim rngList As Range
Dim strSelected As String
Dim LastRow As Long

      If cbSTATE.ListIndex <> -1 Then
           strSelected = cbSTATE.Value
            If ToggleButton1.Value = True Then
                AcctFilter = "Active"
            Else
                AcctFilter = ""
            End If
           LastRow = Worksheets("MA").Range("A" & Rows.Count).End(xlUp).Row
           Set rngList = Worksheets("MA").Range("S2:S" & LastRow)
           For Each rngSTATE In rngList
                If rngSTATE.Value = strSelected And AcctFilter = "" Then
                    lbOFFICE.AddItem rngSTATE.Offset(, -5)
                    cbCITY.AddItem rngSTATE.Offset(, -1)
                ElseIf rngSTATE.Value = strSelected And rngSTATE.Offset(0, -3).Value = AcctFilter Then
                    lbOFFICE.AddItem rngSTATE.Offset(, -5)
                    cbCITY.AddItem rngSTATE.Offset(, -1)
                End If
           Next rngSTATE
      End If
Me.lbOFFICE.Visible = True
Dim Ray, i As Integer, j As Integer, Temp As String
Ray = lbOFFICE.List
lbOFFICE.Clear
On Error Resume Next
'MsgBox lbOFFICE.List
    For i = 1 To UBound(Ray) - 1
        For j = i To UBound(Ray)
        Debug.Print ("i: " & i & " j: " & j)
            If Ray(j) < Ray(i) Then 'And Ray(j) <> "" And Ray(i) <> "" Then
                    Temp = Ray(i)
                    Ray(i) = Ray(j)
                    Ray(j) = Temp
            End If
        Next j
    Next i
lbOFFICE.List = Ray
end sub





What is lbOFFICE & how are you populating it?
Also how are you calling the code you've supplied?
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,437
Office Version
  1. 365
Platform
  1. Windows
What is lbOFFICE?
Is it a listbox?
If so is it on a userform, or on a sheet. If it's on a sheet is it a Forms control on an activeX?
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try altering your sorting code as below:-
Code:
Dim Ray As Variant, i As Integer, j As Integer, Temp As String
 Ray = LBOffice.List
 LBOffice.Clear
 For i = 0 To UBound(Ray)
    For j = i To UBound(Ray)
        If Ray(j, 0) < Ray(i, 0) And Ray(j, 0) <> "" Then
            Temp = Ray(i, 0)
            Ray(i, 0) = Ray(j, 0)
            Ray(j, 0) = Temp
        End If
    Next j
 Next i
 LBOffice.List = Ray
 
Last edited:

jjpski

New Member
Joined
Jan 4, 2011
Messages
29
This works perfectly. Awesome!

Thanks.


Try altering your sorting code as below:-
Code:
Dim Ray As Variant, i As Integer, j As Integer, Temp As String
 Ray = LBOffice.List
 LBOffice.Clear
 For i = 0 To UBound(Ray)
    For j = i To UBound(Ray)
        If Ray(j, 0) < Ray(i, 0) And Ray(j, 0) <> "" Then
            Temp = Ray(i, 0)
            Ray(i, 0) = Ray(j, 0)
            Ray(j, 0) = Temp
        End If
    Next j
 Next i
 LBOffice.List = Ray
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,275
Messages
5,853,028
Members
431,541
Latest member
Jeffwill22

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