containing filter macro

[Binky]

Hi all,

I am trying to make a button to use the containing filter on my spreadsheet. From what I have read on the web the macro I have should work but it is filtering out all the data. When I put Criteria1:="*=civil*" in the spreadsheet I get the correct cells being shown but then if I change it to Criteria1:=str and put civil in cell B5 I get nothing. I am a VB noob so any help would be greatly appreciated.



Sub search()
'
' search Macro
'
Dim str As String
str = Range("B5").Value

ActiveSheet.Range("$A$10:$D$490").AutoFilter Field:=3, Criteria1:=str _
, Operator:=xlAnd
End Sub

 

[VoG]

Try

ActiveSheet.Range("$A$10:$D$490").AutoFilter Field:=3, Criteria1:="*" & Str & "*", Operator:=xlAnd

 

[Binky]

Just tried this and it works perfectly. Thanks so much!