autofilter partial match

[tbettar]

Hi there,

I'm new to visual basics and I'm trying to filter my data so it will show up as the User searches for it. However, I want the User to be able to search for partial matches.

Example: Product Number in the table is 1234567
I want the User to be able to simply search for 123 and the item with the 1234567 Product Number to show up.

Example: Name in the table is John Smith
I want the User to be able to simply search for John and all names that include John to show up.

So I would need a partial match for AutoFilter.

This is what I have:

If FieldName = "Product Number" Then
'filter by Product Number
MyTable.Range.AutoFilter Field:=1, Criteria1:=UserInput

ElseIf FieldName = "Area" Then
'filter by Area
MyTable.Range.AutoFilter Field:=2, Criteria1:=UserInput

ElseIf FieldName = "Name" Then
'filter by Name
MyTable.Range.AutoFilter Field:=5, Criteria1:=UserInput

This code only work if the user searches for the exact cell content.

Thank you!

 

[davesexcel]

try
"*" & UserInput & "*"

 

[tbettar]

try
"*" & UserInput & "*"

Would I write it as:
Criteria1:= “*” & UserInput & “*” ??