Search box code doesnt select correct cell on worksheet

[ipbr21054]

Morning,


I have taken this code from another worksheet & made the edits for it to work on my new sheet.
I type in the search box say AB & all the results with AB are shown in the listbox.
I select the item i require.

My problem is that the item i selected isnt selected on my worksheet in column E
In fact what is selected is the side bar.

Can you advise what i missed in the code so the cell in column E should be selected.
Thanks

Private Sub TextBox1_Change()
  Dim r As Range, f As Range, Cell As String, added As Boolean
  Dim sh As Worksheet
  
  Set sh = Sheets("MC VIN")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 5
    .ColumnWidths = "100;0"
    If TextBox1.Value = "" Then Exit Sub
    Set r = Range("E11", Range("E" & 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.Row
              added = True
              Exit For
          End Select
        Next
           If added = False Then
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Row
        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> Cell
      TextBox1 = UCase(TextBox1)
      Else
      MsgBox "NO VIN MODEL WAS FOUND USING THAT INFORMATION", vbCritical, "VIN MODEL SEARCH MESSAGE"
      TextBox1.Value = ""
      TextBox1.SetFocus
    End If
  End With
End Sub

 

[ipbr21054]

Just an update.
i found this code which now takes me to column E
The issue now is finding why it doesnt select the correct item of which i had selected from the list box

Private Sub ListBox1_Click()
  Range("E" & ListBox1.List(ListBox1.ListIndex, 1)).Select
  Unload HondaMcVinSearch
End Sub

 

[ipbr21054]

Hi,
I have found the issue but need some advice if i may to fix it.

If in my database there is only 1 occurance of say AZ1 then when i search for AZ1 i select it in the listbox & i am taken to it no problem.

However if there are say 5 occurancies of AZ1 then all AZ1 are shown in the list box but selecting one of them doesnt take me to the correct item selected.
As a test i did this.
AZ1 1
AZ1 2
AZ1 3
AZ1 4
AZ1 5

Now when i search AZ1 i see all the AZ1-5 options in the listbox.
Selecting one then i am taken to it no problem.

So can you advise an edit so with the original results
AZ1
AZ1
AZ1
AZ1
AZ1
No matter which i select i MUST be taken to the first AZ1 in the worksheet.

This will then be ok for me

Thanks

 

[ipbr21054]

Any advice please.