AutoFilter with Variable

[AlexCS]

Hi everyone,

Could someone please explain to me why the following does not work?

ActiveSheet.Range("$A$1:$Z$5000").AutoFilter Field:=8, Criteria1:=Array("*New_Company*"), Operator:=xlFilterValues

New_Company is a variable of type String that takes different values via an InputBox

How could I correctly formulate the instruction to get to the desired result - that is filter my tab on a variable to get all the entries containing that variable in their name?

Many thanks,

Alex

 

[Andrew Poulsom]

Try:

ActiveSheet.Range("$A$1:$Z$5000").AutoFilter Field:=8, Criteria1:="*" & New_Company & "*", Operator:=xlFilterValues

 

[AlexCS]

Thanks a lot Andrew! I had instructed my users to input the variable directly between stars but this works as well and I've learned something new