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

 

[mikerickson]

Have you tried converting the range to a Table and filtering the column you want for the value you want.

 

[ipbr21054]

I havent no as i wasnt sure how to

 

[mikerickson]

Select the range, and press Format As Table on the Home ribbon.

 

[ipbr21054]

Morning,
I misunderstood your question so i understand now the question.

In the mean time i had made a start & using textboxes wit hresuolts showing in a listbox.
All works fine apart from one thing & maybe you can assist.

Lets assume we have TextBoxes1 to 5
Each TextBox refers to a certain column.
I enter a value in the TextBox in question & results are then shown in ListBox1
Selecting a value in ListBox1 then should take me to that value on my worksheet but this is my problem.

Example.
TextBox2 i enter ABC
Results that contain ABC are shown in ListBox1.
I make a selection & im then taken to the correct row on my worksheet BUT the cell in column A is selected as opposed to my selection which in this case is C
I understand what is happening BUT need some help to select the correct column.

Here is my code.in use.

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

This code works fine & i can see why it selects the cell in column A every time.

Basically.
TextBox1 column A
TextBox2 column D
TextBox3 column R
TextBox4 column P

When i am searching for TextBox3 using the code about takes me to the correct row but selects cell A where i need it to select cell R
Same applies to the others.

Hope i explained correctly

 

[mikerickson]

You have a two column list box and the listBox click event refers to ListBox1.List(Listbox1.ListIndex, 2))
List boxes are 0 based, the code that fills the listbox correctly refers to the second column with the index 1.
Change the 2 in the quoted line to a 1.

 

[ipbr21054]

Hi,
I obviously didnt explain correctly.
See my supplied screen shot to advise.

My textboxes refer to the headings shown in row 7

Currently if i search for call it "SEARCH INFO" value XYZ which is in column D i make the selection in the ListBox1 & im taken to the correct row BUT the cell in COLUMN A is selected.

If i search for call it "SEARCH WORD" value ABC123 which is column F i make the selection in the ListBox1 & im taken to the correct row BUT the cell in COLUMN A is selected.

Basically what im looking for is to be still taken to the correct row on the worksheet BUT dont keep selecting the cell in column A select the SEARCH INFO value itself

Did i explain better this time.

So looking at my screen shot if i search for XYZ then when taken to worksheet select the cell that has XYZ in it, so row 1673 column D
Do not select row 1673 column A

 

[mikerickson]

Once you've found the column A cell, you could use Offset or EntireRow to select the proper column.
If you know that the search term is somewhere on that row, you could use .Find to find the cell that it's in.

 

[ipbr21054]

Hi,
Thats my issue in that i dont know how to write it.
I think offset sounds better as i know column A is then x amount of columns from the column its going to search.

So if im searching from something in coloumn F then the code will take me to the cell in A but then the offset would be -7

The next search for something in column C would be -4
The issue is knowing what to write & advising which offset is for which search.

 

[mikerickson]

if myCell is the column A cell

myCell.Offset(0, 5).Select will select the cell in column F of the same row. As will myCell.Cells(1, 6).Select and myCell.EntireRow.Range("F1").Select