Advice for generic code to search a specific column

[ipbr21054]

Morning,
I am using the code shown below.
I originally only needed to search for a customers name in column A hence the code shown.

I am now finding myself needing to search for other things which are in different columns as my database has become much larger.
Example.

Search for....
Car registration number in column B
Vehicle make in column D
Key code in column J

etc etc

Please can you advise a generic code that would allow me to type in a Textbox for the item to be searched but im not sure how to go about advising which column it should be searched in

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
    
  Dim r As Range, f As Range, cell As String, added As Boolean
  Dim sh As Worksheet

  Set sh = Sheets("DATABASE")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 2
    .ColumnWidths = "100;0"
    If TextBox1.Value = "" Then Exit Sub
    Set r = Range("A5", Range("A" & 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)
      .TopIndex = 0
      Else
      MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "DATABASE SHEET CUSTOMER NAME SEARCH"
      TextBox1.Value = ""
      TextBox1.SetFocus
    End If
  End With
  End If
End Sub

 

[ipbr21054]

Thanks but who do i wite it with the code as each search is a different column.

If i use that code above how does the code know to offset the next search for column D

Do you see what im getting at ?

 

[ipbr21054]

Morning,
Im still trying to get this to work so can you advise the code in full please.
I cant follow what i need to do from the small code you supply,my learning difficulties just dont allow it.

Code i have so far

Private Sub ListBox1_Click()
  Set sh = Sheets("DATABASE")
  sh.Select
  Range("A" & ListBox1.List(ListBox1.ListIndex, 2)).Select
  Unload DatabaseSearchForm
End Sub

The above works & takes me to the correct row & the cell in column A is selected.
But the part im stuck with is the extra code needed to select the cell on the row of which is what was selected from the listbox.

So like this BUT the correct way.

If ListBox1.value = TextBox1.value Then
Offset 3 "This then should select the cell in column B"
Else

If ListBox1.value = TextBox2.value Then
Offset 5 "This then should select the cell in column D"
Else

If ListBox1.value = TextBox3.value Then
Offset 11 "This then should select the cell in column J"
Else

If ListBox1.value = TextBox4.value Then
Offset 13 "This then should select the cell in column L"
End If

As you can see i am unable to write it correctly BUT with some help for the correct code i am then able to follow along & edit etc.
Thats the hurdle i face each day.

Thanks

 

[ipbr21054]

Or
Once on worksheet at the correct row just find in that row what was selected from listbox1

So easy in my head but cant put in on paperwork