Search certain word to filter a table using vba.

[zulzzz88]

Hi, I want to create a search box to make it easier for my colleague to find information from 1 table. However, from my current vba module, they need to know the exact starting word to find what they are looking for. How can I set it so that they can only write certain words in the name? For example, they can just key in “motor”, in other to look for list “air bearing motor”.

Sub Searchable_List()

Dim Mainsheet As Worksheet
Dim Inventory As ListObject

Set Mainsheet = Sheets("Search")
Set Inventory = Mainsheet.ListObjects("Inventory")

UserInput = Mainsheet.Range("C3") & "*"
Fieldname = Mainsheet.Range("C5").Value

'Check if there is any user input.
If UserInput <> "" Then
    'User input a value,so,proceed
    
    'Clear any filters.
    Inventory.AutoFilter.ShowAllData
    
    'Apply new filters.
    If Fieldname = "Part Number" Then
        'Filter by Part Number
        
        Inventory.Range.AutoFilter Field:=1, Criteria1:=UserInput
        
    ElseIf Fieldname = "Part Name" Then
        'Filter by Part Name
        
        Inventory.Range.AutoFilter Field:=2, Criteria1:=UserInput
        
    ElseIf Fieldname = "Material Code" Then
        'Filter by Material Code
        
        Inventory.Range.AutoFilter Field:=3, Criteria1:=UserInput
        
    End If

End If

      
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

UserInput = "*" & Mainsheet.Range("C3") & "*"

 

[zulzzz88]

Hi & welcome to MrExcel.
How about

UserInput = "*" & Mainsheet.Range("C3") & "*"

Hai, tq for reply. Its work for me now.

However, I have 1 more question to ask. Why I only can search for item that have text format only? When i try to filter that only have numbering, it shows nothing?

For example,

List Item
1. 1N2022
2. 191101
3. 20NN23

From above list, only item no 1 and no 3 will show up, but when i search item no 2, it shows nothing. Is that because there is no value setting in the coding?

Tq.

 

[Fluff]

You can only use wild card matching with text.