Search for LIKE

  • Thread starter Thread starter magmike
  • Start date Start date
M

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
 
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,
Click to expand...

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?
 
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
Click to expand...

"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?
 
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.
Click to expand...

It would need to match whatever the user had entered in either A2 or B2.
How would you do that?
 
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.
Click to expand...

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