My sorted sheet isnt the same order when Listbox is populated

[ipbr21054]

On a command button i have the following code.
It sorts my sheet column D from A-Z
Once done my userform opens
I press a command button to now load the values from the sheet that has just been sorted column D A-but Listbox isnt the same order.
This is the code.

Private Sub CheckConnectorsUsed_Click()

  Dim r As Range, f As Range, Cell As String, added As Boolean
  Dim sh As Worksheet
  Dim i As Long
  Set sh = Sheets("MCLIST")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 4
    .ColumnWidths = "100;170;70;10"
   
    Set r = Range("C8", Range("C" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBox1.Value, LookIn:=xlValues, LookAt:=xlPart)
    If Not f Is Nothing Then
      Cell = f.Address
      Do
        added = False
        For i = 0 To .ListCount - 1
          Select Case StrComp(.List(i), f.Value, vbTextCompare)
            Case 0, 1
              .AddItem f.Value, i
              .List(i, 1) = f.Offset(, 1).Value ' MODEL
              .List(i, 2) = f.Offset(, 6).Value ' YEAR
              .List(i, 3) = f.Offset(, 9).Value ' CONNECTOR USED
              .List(i, 4) = f.Row
              added = True
              Exit For
          End Select
        Next
        If added = False Then
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Offset(, 1).Value ' MODEL
          .List(.ListCount - 1, 2) = f.Offset(, 6).Value ' YEAR
          .List(.ListCount - 1, 3) = f.Offset(, 9).Value ' CONNECTOR USED
          .List(.ListCount - 1, 4) = f.Row
        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> Cell
      .TopIndex = 0
      Else
    End If
    End With

End Sub

Here is a screen shot of my partial sheet & then Listbox
Im expecting the Listbox to look the same as the sheet

The first value is correct AFRICA TWIN but then it starts in reverse being XL700 when it should be starting at CB1000



Please advise what went wrong thanks.

 

[RoryA]

Your code is checking column C (the one with Honda) against the list contents. If the value is the same or 'lower' (alphabetically) than the current row in the list, it inserts a new row at that point.

If the list is already sorted on the worksheet, I don't know why you are bothering with your StrComp loop - just add the items to the listbox as you find them.

 

[NoSparks]

Same issue you had here

 

[ipbr21054]

The items are sorted A-Z on the sheet correctly.
I don’t know how to just put them into listbox.
As you see in the code supplied.
It looks at Textbox1 which is HONDA then once found in the sheet out the values from the few other columns in the Listbox.

I thought I had to do what I put ?

 

[RoryA]

Replace this:

    If Not f Is Nothing Then
      Cell = f.Address
      Do
        added = False
        For i = 0 To .ListCount - 1
          Select Case StrComp(.List(i), f.Value, vbTextCompare)
            Case 0, 1
              .AddItem f.Value, i
              .List(i, 1) = f.Offset(, 1).Value ' MODEL
              .List(i, 2) = f.Offset(, 6).Value ' YEAR
              .List(i, 3) = f.Offset(, 9).Value ' CONNECTOR USED
              .List(i, 4) = f.Row
              added = True
              Exit For
          End Select
        Next
        If added = False Then
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Offset(, 1).Value ' MODEL
          .List(.ListCount - 1, 2) = f.Offset(, 6).Value ' YEAR
          .List(.ListCount - 1, 3) = f.Offset(, 9).Value ' CONNECTOR USED
          .List(.ListCount - 1, 4) = f.Row
        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> Cell
      .TopIndex = 0
      Else
    End If

with this:

    If Not f Is Nothing Then
      Cell = f.Address
      Do
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Offset(, 1).Value ' MODEL
          .List(.ListCount - 1, 2) = f.Offset(, 6).Value ' YEAR
          .List(.ListCount - 1, 3) = f.Offset(, 9).Value ' CONNECTOR USED
          .List(.ListCount - 1, 4) = f.Row
        Set f = r.FindNext(f)
      Loop While f.Address <> Cell
      .TopIndex = 0
    End If

 

[ipbr21054]

Ok Thats looks much better RoryA but i see the first entry last,other than that all is ok.

 

[RoryA]

Change the first Find part to:

Set f = r.Find(TextBox1.Value, After:=r.Cells(r.count), LookIn:=xlValues, LookAt:=xlPart)

 

[ipbr21054]

If you mean so its like this.

Private Sub CheckConnectorsUsed_Click()
  Dim r As Range, f As Range, Cell As String, added As Boolean
  Dim sh As Worksheet
  Dim i As Long
  Set sh = Sheets("MCLIST")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 4
    .ColumnWidths = "100;170;70;10"
    
    Set f = r.Find(TextBox1.Value, After:=r.Cells(r.Count), LookIn:=xlValues, LookAt:=xlPart)
    Set f = r.Find(TextBox1.Value, LookIn:=xlValues, LookAt:=xlPart)
    
    If Not f Is Nothing Then
      Cell = f.Address
      Do
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Offset(, 1).Value ' MODEL
          .List(.ListCount - 1, 2) = f.Offset(, 6).Value ' YEAR
          .List(.ListCount - 1, 3) = f.Offset(, 9).Value ' CONNECTOR USED
          .List(.ListCount - 1, 4) = f.Row
        Set f = r.FindNext(f)
      Loop While f.Address <> Cell
      .TopIndex = 0
    End If
    End With

End Sub

I then get this , with that new line in yellow when i debug

 

[RoryA]

You replaced the wrong line. You need to keep the line that sets r and replace the r.Find line after that.

 

[ipbr21054]

Sorted Thanks