Auto Filter problem

[scotttt]

Hi
I want to use the value that is input into textbox1 to filter on.
It should filter all the data that contains the value in the textbox.
I wrote a macro to record the contains filter and was given this:

Selection.AutoFilter Field:=1, Criteria1:="=**", Operator:=xlAnd

I have tried to modify it but cannot get the conatins part to work

The code below I thought would work but the filter type is "ends with" rather than contains. The value is picked up correctly from textbox1.

Selection.AutoFilter Field:=1, Criteria1:="=**" & TextBox1.Value, Operator:=xlAnd


Any Advice?

[Zack Barresse]

Hi scott, welcome to the board!


Do you think you could post your entire code, along with a zipped example of your workbok? And don't forget to check out the VBA tags when posting (created by our own Mark007!) that make your code look very good!

[scotttt]

The file is real big but I can make a small version.

Ok done it. real basic but demonstartes my problem.
In collumn A there are numbers written one, two, three, etc

If you click on the button it brings up a user form that if you type in the letter E and run I want to to filter on all data in collumn A that contains the letter E.

I have tried numerous variations to the code but have no joy.
This code takes the letter from the textbox in the user form but filters it as "ends with".

Hope you can help Ive been after a solution all day!

[Zack Barresse]

Just add the second asteriks after the value ...

Criteria1:="=*" & TextBox1.Value & "*"

So your routine might look something like this ...

Private Sub CommandButton1_Click()
    Range("A1").AutoFilter Field:=1, Criteria1:="=*" & TextBox1.Value & "*", Operator:=xlAnd
    UserForm1.Hide
End Sub

(No need to select ..)

[scotttt]

Many thanks for that!

I thought I tried all combinations with the *, =, & textbox1 but obviously not.

Frustration over with!

Thanks Again

Scott

[Ken Puls]

Hi Scott,

Just an FYI, I've changed your thread from "Closed" to "Solved". This will still let someone post to it, just in case they have a gem they want to share, and also marks it so that others know you're A-Okay!

If you still think it should be "Closed" (to prevent others from posting to it), by all means go ahead though!

Cheers,