Listbox value not sorted correctly

[ipbr21054]

Evening,
Please can you advise where i am going wrong.

Textbox1 Searches column B for a vehicle.
The other values in the listbox are also puuled from my worksheet.
As you can see its all over the place.

I typed A to pull any vehicle from column B that has an A in it & for some reason its also pulled ID48 GLASS which is from column C & also the JHMAP is from column F

Screenshot attached
Code currently in use.

Private Sub TextBox1_Change()
  Dim r As Range, f As Range, cell As String, added As Boolean
  Dim sh As Worksheet
  
  Set sh = Sheets("KDX2CLONING")
  sh.Select
  With ListBox1
      .Clear
    .ColumnCount = 7
    .ColumnWidths = "190;150;75;120;110;125"
    If TextBox1.Value = "" Then Exit Sub
    Set r = Range("B4", Range("G" & 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, 6) = f.Row                'ROW NUMBER
              .List(i, 1) = f.Offset(, 1).Value  '
              .List(i, 2) = f.Offset(, 2).Value  '
              .List(i, 3) = f.Offset(, 3).Value  '
              .List(i, 4) = f.Offset(, 4).Value  '
              .List(i, 5) = f.Offset(, 5).Value  '

              added = True
              Exit For
          End Select
        Next
        If added = False Then
              .AddItem f.Value
              .List(.ListCount - 1, 6) = f.Row                'ROW NUMBER
              .List(.ListCount - 1, 1) = f.Offset(, 1).Value  '
              .List(.ListCount - 1, 2) = f.Offset(, 2).Value  '
              .List(.ListCount - 1, 3) = f.Offset(, 3).Value  '
              .List(.ListCount - 1, 4) = f.Offset(, 4).Value  '
              .List(.ListCount - 1, 5) = f.Offset(, 5).Value  '
              
        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
      TextBox1 = UCase(TextBox1)
      .TopIndex = 0
      Else
      MsgBox "NO VEHICLE WAS FOUND USING THAT INFORMATION", vbCritical, "KDX2 VEHICLE SEARCH"
      TextBox1.Value = ""
      TextBox1.SetFocus
    End If
  End With
End Sub

 

[ipbr21054]

Morning,
Ive been looking through this code again but dont see why the listbox is showing values from other columns.
I search for a vehicles in column B & other values from other columns are also being put in the listbox that are not in column B

Do you see an issue.
Thanks

 

[BrianJN1]

I seems to me, not having made any real usage of OFFSET that you have omitted a parameter in each offset.
- offset(, 1) etc
Maybe that should read as offset(0, 1) or maybe offset(i, 1) etc.

 

[ipbr21054]

THnka,
I thionk what i did above was heading in the correction but ive now sorted it using pretty much the same.
As shown below.

Private Sub TextBox1_Change()
  TextBox1 = UCase(TextBox1)
  Dim r As Range, f As Range, cell As String, added As Boolean
  Dim sh As Worksheet
  
  Set sh = Sheets("KDX2LIST")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 7
    .ColumnWidths = "160;200;90;110;180;135"

    If TextBox1.Value = "" Then Exit Sub
    Set r = Range("B4", Range("B" & 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, 6) = f.Row                'ROW NUMBER
              .List(i, 1) = f.Offset(, -1).Value 'CUSTOMER
              .List(i, 2) = f.Offset(, 2).Value  'YEAR
              .List(i, 3) = f.Offset(, 3).Value  'DATE
              .List(i, 4) = f.Offset(, 4).Value  'VIN
              .List(i, 5) = f.Offset(, 5).Value  'TOOL USED

              added = True
              Exit For
          End Select
        Next
        If added = False Then
          .AddItem f.Value
              .List(.ListCount - 1, 6) = f.Row                'ROW NUMBER
              .List(.ListCount - 1, 1) = f.Offset(, -1).Value 'CUSTOMER
              .List(.ListCount - 1, 2) = f.Offset(, 2).Value  'YEAR
              .List(.ListCount - 1, 3) = f.Offset(, 3).Value  'DATE
              .List(.ListCount - 1, 4) = f.Offset(, 4).Value  'VIN
              .List(.ListCount - 1, 5) = f.Offset(, 5).Value  'TOOL USED

        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
      TextBox1Search = UCase(TextBox1Search)
      .TopIndex = 0
      Else
      MsgBox "NO INFO WAS FOUND", vbCritical, "KDX2 SEARCH VEHICLE"
      TextBox1.Value = ""
      TextBox1.SetFocus
    End If
  End With
End Sub