Excel Dynamic Search across 2 columns

[unkn0wn995]

Hi Everyone,

New to this forum, as well as understanding VBA in excel,


Basically, I've got a tooling spreadsheet, and I've created a dynamic search box , where based on operators input they can search for item they need (it only searches column A currently), it gets filtered with help of VBA code.
But now I've been requested to change it, so operators searches as column A and B (so if they type either part name or part number it will search by either of columns and show results (as operator might go by name of whatever is in column A or by part code/name in Column B)
My only issue is I can't seems to figure out how to change my search box / VBA code to make sure it search and filters out by either column A/B.

I've included a picture of spreadsheet as well as VBA code, so one of you excel geniuses can help me with this.

 

[Georgiboy]

Welcome to the forum,

You are welcome to give the below a try:

Private Sub TextBox1_Change()
    Dim lo As ListObject, str As String
    
    str = "*" & Me.TextBox1.Text & "*"
    Set lo = Sheet1.ListObjects("Data")
    
    With Application
        If str = "**" Then
            lo.AutoFilter.ShowAllData
        ElseIf .CountIf(lo.ListColumns("Tool Description").DataBodyRange, str) > 0 Then
            lo.Range.AutoFilter 1, str, xlFilterValues
        ElseIf .CountIf(lo.ListColumns("Part Code").DataBodyRange, str) > 0 Then
            lo.Range.AutoFilter 2, str, xlFilterValues
        Else
            lo.AutoFilter.ShowAllData
        End If
    End With
    
End Sub

It will look in the first column, if there are no values found then it will look in second column, if textbox is made blank it will return remove the filter. It's not perfect but it may do what you need.

 

[unkn0wn995]

Welcome to the forum,

You are welcome to give the below a try:

Private Sub TextBox1_Change()
    Dim lo As ListObject, str As String
   
    str = "*" & Me.TextBox1.Text & "*"
    Set lo = Sheet1.ListObjects("Data")
   
    With Application
        If str = "**" Then
            lo.AutoFilter.ShowAllData
        ElseIf .CountIf(lo.ListColumns("Tool Description").DataBodyRange, str) > 0 Then
            lo.Range.AutoFilter 1, str, xlFilterValues
        ElseIf .CountIf(lo.ListColumns("Part Code").DataBodyRange, str) > 0 Then
            lo.Range.AutoFilter 2, str, xlFilterValues
        Else
            lo.AutoFilter.ShowAllData
        End If
    End With
   
End Sub

It will look in the first column, if there are no values found then it will look in second column, if textbox is made blank it will return remove the filter. It's not perfect but it may do what you need.

Thank you for this, works great to some degree, my only issues is that if i search for something, for results to come up from column B, I have to be typing it up exactly word for word otherwise it won't show up.
If there a way to change this, so it only show like a partial search / or maybe i can type in first few matching letters/number for it to show up?

For example :
In column A i have text saying: "M2.5 x 0.45 L/S Tap" and Column B saying: "OSG 11768 EX-LT-SFT"

If i was to search by 11768 or just OSG (from information based in column B, it doesn't bring anything up at all.

I hope i explained it well enough.

 

[Georgiboy]

Strange, in the scenario I have set up: 11768 & OSG return a result from column B.

Have you amended the code in any way, if so can you post how the code looks now?

Does the str line still look as below:

str = "*" & Me.TextBox1.Text & "*"

 

[Georgiboy]

Oh maybe it is still finding values from column A when you want to search column B, have you thought about having two input boxes in the form, one to search each column?

That way when you search in one input box the code could clear the other and vice versa.

The other option may be to have a column that concat's the value in columns A & B with a formula, the search box could then return results from the concat column.

 

[unkn0wn995]

No the code is untouched
The only thing i've added is protect/unprotect sheet, as I've got some locked off cells that i don't want people to edit, wherever that is causing issue, that's only thing i could think off?

 

[Georgiboy]

For example if you add another textbox named: Textbox2 to search the second column, you could try something like the below:

Private Sub TextBox1_Change()
    Dim lo As ListObject, str As String
    
    str = "*" & Me.TextBox1.Text & "*"
    Set lo = Sheet1.ListObjects("Data")
    
    With Application
        If str = "**" Then
            lo.AutoFilter.ShowAllData
        ElseIf .CountIf(lo.ListColumns("Tool Description").DataBodyRange, str) > 0 Then
            lo.Range.AutoFilter 1, str, xlFilterValues
        Else
            lo.AutoFilter.ShowAllData
        End If
    End With
End Sub
Private Sub TextBox1_Enter()
    Me.TextBox2 = ""
End Sub

Private Sub TextBox2_Change()
    Dim lo As ListObject, str As String
    
    str = "*" & Me.TextBox2.Text & "*"
    Set lo = Sheet1.ListObjects("Data")
    
    With Application
        If str = "**" Then
            lo.AutoFilter.ShowAllData
        ElseIf .CountIf(lo.ListColumns("Part Code").DataBodyRange, str) > 0 Then
            lo.Range.AutoFilter 2, str, xlFilterValues
        Else
            lo.AutoFilter.ShowAllData
        End If
    End With
End Sub
Private Sub TextBox2_Enter()
    Me.TextBox1 = ""
End Sub

 

[unkn0wn995]

Strange, in the scenario I have set up: 11768 & OSG return a result from column B.

Have you amended the code in any way, if so can you post how the code looks now?

Does the str line still look as below:

str = "*" & Me.TextBox1.Text & "*"

The strange thing is, it will search by some words and not the others, and also if you search by what its already typed up in column B (as in OSG 11768 EX-LT-ST) it doesn't even come up.

Would i maybe need to format cells as text or something possibly?

 

[Georgiboy]

Did you give post 7 any thought?

It may be a sleeker option.

 

[unkn0wn995]

Did you give post 7 any thought?

It may be a sleeker option.

I'll give it a try, but since a lot of people were going to be using it, I was hoping for a single search bar to make it easier, but if i have no options then i'll just use the 2 search boxes.