Search for LIKE

[magmike]

In the code of my sheet, it returns only rows where the field is exactly what B2 is, but I want partial matches or contained matches to result - i.e. 'fal' would return "Idaho Falls","Buffalo" and "Falls Church". How would I modify this code?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A2:B2")) Is Nothing _
Then Exit Sub
Dim myRange As Range

'Set the range of your table
Set myRange = Range("A4:E100")
If Target <> "" Then
myRange.AutoFilter field:=Target.Column, Criteria1:=Target
Else
myRange.AutoFilter field:=Target.Column, Criteria1:="<>"
End If
End Sub

Thanks in advance for your help,
magmike

 

[isabelle]

hi magmike,

Criteria1:="=*fal*"

isabelle

Le 2013-09-06 23:04, magmike a écrit :

In the code of my sheet, it returns only rows where the field is exactly what B2 is,

but I want partial matches or contained matches to result - i.e. 'fal'
would return "Idaho Falls","Buffalo" and "Falls Church". How would I
modify this code?

 

[magmike]

In the code of my sheet, it returns only rows where the field is exactly what B2 is, but I want partial matches or contained matches to result - i.e.. 'fal' would return "Idaho Falls","Buffalo" and "Falls Church". How would I modify this code? Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A2:B2")) Is Nothing _ Then Exit Sub Dim myRange AsRange 'Set the range of your table Set myRange = Range("A4:E100") If Target <> "" Then myRange.AutoFilter field:=Target.Column, Criteria1:=Target Else myRange.AutoFilter field:=Target.Column, Criteria1:="<>" End IfEnd Sub Thanks in advance for your help, magmike

"fal" was just an example so you could see how i wanted it to search. It would need to match whatever the user had entered in either A2 or B2. How would you do that?

 

[isabelle]

hi magmike

Criteria1:="=*" & [A2] & "*", Operator:=xlOr, Criteria2:="=*" & [B2] & "*"

isabelle

Le 2013-09-07 01:21, magmike a écrit :

"fal" was just an example so you could see how i wanted it to search.

It would need to match whatever the user had entered in either A2 or B2.
How would you do that?

 

[isabelle]

if the active sheet is not the sheet with the filter

With Sheets("Feuil1").Range("$A$1")
..AutoFilter Field:=1, Criteria1:="=*" & .Range("A2") & "*",
Operator:=xlOr, Criteria2:="=*" & .Range("B2") & "*"
End With

isabelle

Le 2013-09-07 01:59, isabelle a écrit :

hi magmike

Criteria1:="=*" & [A2] & "*", Operator:=xlOr, Criteria2:="=*" & [B2] & "*"

isabelle

Le 2013-09-07 01:21, magmike a écrit :

"fal" was just an example so you could see how i wanted it to search.

It would need to match whatever the user had entered in either A2 or B2.
How would you do that?