VBA autofilter "contains" dynamic criteria

[rob5497]

Hi,

I am trying to write a line of code to autofilter that will return the correct number of records if these records "contain" a certain criteria.

The criteria is provided by the user using a ComboBox that is populated with the relevant ID number of the record. There are multiple records in the range "VacData" that may contain the ID number. I'd like it to return all instances. As an example, the user may select ID number "156-18-1234" from the ComboBox - my code "holds" this in G22 on a sheet called "Lookups". But, there may be child records such as "156-18-1234-1" and "156-18-1234-2". In this example I'd like the code to filter me all 3 records.

I can't figure out how to insert the "contains" wildcard into my code whilst respecting the dynamic contents of G22. Removing the wildcard returns only the parent record of "1234" and not the additional two child records.

If ActiveWorkbook.Worksheets("Lookups").Range("G22") <> Empty Then
ActiveSheet.Range("VacData").AutoFilter Field:=1, Criteria1:=ActiveWorkbook.Worksheets("Lookups").Range("*" & G22 & "*").Value, _
Operator:=xlAnd
Else: ActiveSheet.Range("VacData").AutoFilter Field:=1
End If

If anyone can help, I'd be mighty grateful...it's got me stumped! Thanks in advance,

Rob

 

[Michael M]

Maybe this

Dim strId String
strId = "1234"
activesheet.Range("VacData").AutoFilter Field:=1, Criteria1:="=*" & strId & "*"

 

[rob5497]

Well, doesn't that just work like a dream?! I used strID to dynamically hold the value of the comboBox, using strID = UserForm2.ComboBox23.Value and used the rest of your code as is.

Thank you so much Michael.

Rob

 

[Michael M]

Thanks for the feedback...