Alphabetize a list

[jjpski]

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

 

[hotabae]

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.

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]

try

Ray = Application.Transpose(lbOFFICE.List)

 

[jjpski]

I have tried this and get this error. Run-Time error 13. Type mismatch

try

Ray = Application.Transpose(lbOFFICE.List)

 

[jjpski]

This is all the output.

i: 1 j: 1

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.

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]

What is lbOFFICE & how are you populating it?
Also how are you calling the code you've supplied?

 

[jjpski]

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?

 

[Fluff]

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]

Try altering your sorting code as below:-

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

 

[jjpski]

This works perfectly. Awesome!

Thanks.

Try altering your sorting code as below:-

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