I have a table that i am auto filter based on a cell value. the problem i am coming up against is that unless the cell value match's the filter doesnt work. For example in my table i have mothercare in the 3rd column but i want my users to just be able to search for mother for example.
Sheets("Alpha Report").Select
ActiveSheet.ListObjects("Alpha_Table").Range.AutoFilter Field:=3, Criteria1:= _
Sheets("Search Engine").Range("Customer_Search").Value
I have a feeling that this is something really simple that i am missing
You can use wildcards in filters.
So either auto add wildcards to the criteria like
Criteria1:=Sheets("Search Engine").Range("Customer_Search").Value & "*"
but then you are not able to search eg. for "mother" alone (without finding "motherless" too.
Or the better solution:
Plan a training course for your users on how to use wildcards or describe it at your search sheet! Wildcards can be used also in other searches like Windows search so they might benefit from that.
Also this way the user can decide how to use the wildcard to optimize his/her search. Eg.:
*mother ' ends with mother
mother* ' begins with mother
*mother* ' contains mother