EXCEL SORT CRITERIA AUTOFILTER

[fatboyfatter]

HELP!,
As they say a little knowledge is dangerous, so I need you guys to put me right!
I am trying to automate a filter using macros.
I have set a cell vaue as the FNVARIABLE, this will alwys be an alpha string contained in a title string in a cell
Example 54321A BRKT LH MLDG OTR.
I may wan to find in a list all lines containg BRKT.
I want to use a cell say $A$1 as the input criteria and then autofilter the list blah blah blah!
This is the code I have so far used, when tring to set the autofilter criteria the coding uses an absolute and not the variable I need it to be. when viewing the resultant filter it alwys defaults to "EQUALS" I need it to be "CONTAINS"
Any suggestions?
CODE:
Dim FNVARIABLE
FNVARIABLE = Worksheets("sheet1").Range("A1").Value
Sheets("sheet2)").Select
Columns("c:c").Select
Selection.Copy
Sheets("Sheet3").Select
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFilter Field:=3, Criteria1:=FNVARIABLE, Operator:=xlAnd, _
Criteria2:=FNVARIABLE

 

[SkipVought]

Hi,

Did you try using your macro recorder to record the CONTAINS operator?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fatboyfatter]

Thanks,
Actually I used the recorder to start with, For info if you look at the Code using all the different criteria, when it comes to the 'contains' statement the code looks like this :- ="=*yourstring*", If I substitute any of the Yourstring with FNVARIABLE, when you run the Macro , the search criteria is absolute:- FNVARIABLE, I cannot find any code to subsitute the above to recognise the Alpha code.
Thanks
Paul F

 

[PHV]

="=*" & FNVARIABLE & "*"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fatboyfatter]

Excelent, Works Great!
Thanks Lads for your help
Paul F