Autofilter - Contains

[al]

I'm trying to use macro below to find text in a filtered column using
macro below - it's not working when i input an existing text in the
input box - can someone pls correct my macro - thxs

Sub Macro()
'
'
txt = InputBox("Enter text to find")
'
Selection.AutoFilter Field:=3, Criteria1:="=*txt*",
Operator:=xlAnd
End Sub

 

[FSt1]

hi
what do you do to the sheet before you put text into the input box?
are you high lighting any section of the sheet?
this macro is trying to filter a high lighted section of the sheet that you
say is already filtered.
what do you want it to do?

Regards
FSt1

 

[Jacob Skaria]

Hi

The variable txt should not be in double quotes. The wild card string *
should be in quotes as mentioned below. Also from your code we dont get which
range you have selected. Please try the below.

txt = InputBox("Enter text to find")
Range("C1").AutoFilter field:=3, Criteria1:="*" & txt & "*"

If this post helps click Yes

 

[OssieMac]

A more generic way of doing this is to simply use AutoFilter.Range

Dim txt As String

txt = InputBox("Enter text to find")

txt = "*" & txt & "*"

With ActiveSheet
.AutoFilter.Range.AutoFilter Field:=3, Criteria1:=txt
End With

or you can do it the following way. The advantage of this way is that the
worksheet with the AutoFilter does not have to be the ActiveSheet.

With Sheets("Sheet1")
.AutoFilter.Range.AutoFilter Field:=3, Criteria1:=txt
End With

It does not matter whether you concatenate the asterisks before the
AutoFilter code as I have done or as Jacob did it in his example. I just did
it that way to demonstrate an alternative.

Regards,

OssieMac