AdvancedSearch module to search for more than 1 word?

[KrisNey]

Hello, first time posting...I borrowed a bit of code to create a search box in cell B1. This allows me to search in the Range for a keyword, and it returns matching rows.

I would like to know how to expand this, in order to search for two or three keywords.

Thank you!

Sub MyAdvancedSearch()
Application.ScreenUpdating = False

    Dim r As Range, v As String, s As String
    With Sheets("August")
        .Range("c2").CurrentRegion.EntireRow.Hidden = False
        s = LCase(.Range("b1").Value)
        For Each r In .Range("c4", .Range("c" & .Rows.Count).End(xlUp))
        v = ""
        v = v & r.Value
        v = v & r.Offset(0, 1).Value
        v = v & r.Offset(0, 2).Value
        v = v & r.Offset(0, 3).Value
        v = v & r.Offset(0, 4).Value
        v = LCase(v)
        If Not v Like "*" & s & "*" Then
            r.EntireRow.Hidden = True
        
        End If
    Next r
End With
Application.ScreenUpdating = True
    
End Sub

 

[pgc01]

Hi
Welcome to the board

Do you mean the row must have

both keywords

or

one of the 2 keywords

?

 

[KrisNey]

Oh great question, I would like the row all of the keywords.
Thank you.

 

[KrisNey]

All of the keywords, I meant.
Thank you.

 

[ITWare2008]

Hi KresNey,

I guess this does the trick

=isnumber(search($F$2,C2))*isnumber(search($G$2,C2))

where $f$2 and $g$2 are the words to looking for and C2, C3 and so on are the cells with text to be searched

On cells where both words are met, the function returns 1 whereas none or just one of the words are met the function returns 0, so you notice that cell does not meet your requirements.

Hope this helps and welcome to the board.

Vandalo

 

[pgc01]

Hi

Try replacing the test with:

If Not ((v Like "*" & s1 & "*") And (v Like "*" & s2 & "*")) Then

where s1 and s2 are the 2 keywords

 

[KrisNey]

This worked like a charm, thank you!

 

[pgc01]

I'm glad we could help. Cheers!

 

[KrisNey]

Hello, I am having trouble again. Tried to recreate my sheet, but my 2 keyword search is not working.
Any help is appreciated.

Sub MyAdvancedSearch()
Application.ScreenUpdating = False

    Dim r As Range, v As String, s As String
    With Sheets("Sheet1")
    .Range("c2").CurrentRegion.EntireRow.Hidden = False
    s = LCase(.Range("b1").Value)
    For Each r In .Range("c3", .Range("c" & .Rows.Count).End(xlUp))
    v = ""
    v = v & r.Value
    v = v & r.Offset(0, 1).Value
    v = v & r.Offset(0, 2).Value
    v = v & r.Offset(0, 3).Value
    v = v & r.Offset(0, 4).Value
    v = LCase(v)
    If Not ((v Like "*" & s1 & "*") And (v Like "*" & s2 & "*")) Then
        r.EntireRow.Hidden = True
    End If
Next r
End With
Application.ScreenUpdating = True
    
End Sub