Autofilter from input box

[dan_pafc]

Hi

Im looking to filter a column based on a code the user puts into an input box.

I know the first part of the code should be ok but the second i have just entered, if anyone can help!

Sub CodeFilter()

Dim oS, cl As Range
oS = Application.InputBox("Please Enter Search Criteria")
If oS = "" Then MsgBox "You must Enter a Code": Exit Sub
If oS = "False" Then Exit Sub

For Each cl In Range
Rows("1:1").Select
Selection.AutoFilter Field:=5, Criteria1:="oS", Operator:=xlAnd

End Sub

 

[dan_pafc]

I now have it down to the following which selects row 1 but doesnt filter:

Sub CodeFilter()

Dim oS, cl As Range
oS = Application.InputBox("Please Enter Search Criteria")
If oS = "" Then MsgBox "You must Enter a Code": Exit Sub
If oS = "False" Then Exit Sub

Rows("1:1").Select
Selection.AutoFilter Field:=5, Criteria1:=oS, Operator:=xlAnd

End Sub

 

[Trevor G]

If you look at the following:

dim strInput as string
strinput = Inputbox("Enter your value to filter on")

then when your ready for the filter code add the strInput as the criteria.

Sub mcrFilter()
Dim strInput As String
strInput = InputBox("Enter your value to filter on")
Selection.AutoFilter
ActiveSheet.Range("$A$1:$K$9359").AutoFilter Field:=1, Criteria1:= _
strInput
End Sub

 

[dan_pafc]

If you look at the following:

dim strInput as string
strinput = Inputbox("Enter your value to filter on")

then when your ready for the filter code add the strInput as the criteria.

Sub mcrFilter()
Dim strInput As String
strInput = InputBox("Enter your value to filter on")
Selection.AutoFilter
ActiveSheet.Range("$A$1:$K$9359").AutoFilter Field:=1, Criteria1:= _
strInput
End Sub

Thanks for that Trevor - i need to use the contains strInput - everything I input doesnt work. Any ideas?

 

[Trevor G]

Change the column number in my example to 5 which is indicated in yours then it should work

 

[dan_pafc]

Change the column number in my example to 5 which is indicated in yours then it should work

I have changed to 5. When I go in to the filter, it is using equals rather than contains.

Sorry to be a pest.

 

[Trevor G]

Dan,

You could use the * to search something like this:

*Tea* which would find anything that has text before Tea and also anything after.

Regards

Trevor

 

[jim may]

ActiveSheet.Range("$A$1:$K$9359").AutoFilter Field:=5, Criteria1:= "*" & strInput & "*", Operator:=xlAnd

 

[Peter_SSs]

You could also try:

Columns("E").AutoFilter Field:=1, Criteria1:="*" & strInput & "*"