Serching by Keyword using VBA Code, I need help...

[doms123]

Hi I am using excel 2007 and I am trying to put code inside a button that if i push the button and can search by its keyword from the list. The code I'm generating is this when i record macros

ActiveSheet.Range("$D$1:$D$6").AutoFilter Field:=1, Criteria1:="=*Wayde*", _
Operator:=xlAnd


i need is some how like this but it has an error

ActiveSheet.Range("$D$1:$D$6").AutoFilter Field:=1, Criteria1:=
"=*Sheet1.range(A1).value*", Operator:=xlAnd

Please Help me with the code
Thank you in advance...

PS I have a searching code but it goes something like this...
Sheet5.Range("$A$2:$W$35000").AutoFilter Field:=6

but this code only search by the whole word and not the keyword....

 

[Peter_SSs]

i need is some how like this but it has an error

ActiveSheet.Range("$D$1:$D$6").AutoFilter Field:=1, Criteria1:=
"=*Sheet1.range(A1).value*", Operator:=xlAnd

On the right track.
Try this

ActiveSheet.Range("$D$1:$D$6").AutoFilter Field:=1, Criteria1:="=*" & Sheet1.Range("A1").Value & "*"

 

[doms123]

Thanks Sir... It works!!!