"Contains" filter on number and text column

[tomleitch]

Hi,

I've got a column that I want to filter using "contains" filter in my vba code.

Most of the column data is a 3 digit number - e.g. "842"
But some have more digits/letters - e.g. "84203-A"

Filter input is from a textbox in a userform.

Is there any way I can use an autofilter so that if "842" is entered in the textbox that it will pick up "842" and "84203-A" (i.e. anything that contains "842" at the beginning of it?


Many thanks
Tom

 

[Special-K99]

Does *842* work?

 

[tomleitch]

Doesn't seem to work.

The code I have in my userform is:

ActiveWorkbook.Worksheets("OPS PLANNER").Range(Cells(2, NOTES), Cells(Lrow, NOTES)).AutoFilter Field:=Sys, Criteria1:=TextBox1.Value

I've also tried:

ActiveWorkbook.Worksheets("OPS PLANNER").Range(Cells(2, NOTES), Cells(Lrow, NOTES)).AutoFilter Field:=Sys, Criteria1:="*" & TextBox1.Value & "*"

And neither work.

The first one works, but only brings back an exact match, i.e. filters 842 but not 84201-A or whatever other variant.


Cheers
Tom

 

[alansidman]

How about identifying the cell by using the Instr function within VBA, ie.

If instr(Range("X" & i, "842")) > 0 then
SomeAction

Where X is the column and i is a variable indicating the row

 

[Peter_SSs]

See if this works for you

Dim aCrit As Variant

With ActiveWorkbook.Worksheets("OPS PLANNER").Range(Cells(2, Notes), Cells(Lrow, Notes))
  aCrit = Filter(Application.Transpose(.Value), TextBox1.Value, True)
  .AutoFilter Field:=Sys, Criteria1:=aCrit, Operator:=xlFilterValues
End With