vba find function for excel that searches for multiple keywords

How can you use vba to create a find function that takes a string of keywords and then returns the rows containing all of them. The rows would need to contain all the keywords, but it does not matter how these words are ordered.

I often use the FIND function in excel to find all occurences of keywords, but it cannot be used for multiple keywords that may occur in different order. So for example a search for "hello world" will not find "world hello".
mrandersAsked:
Who is Participating?
 
leonstrykerConnect With a Mentor Commented:
Fair enough, here you go

Leon
Sub FindRows()
Dim rngSearch As Range, rngRow As Range
Dim strString As String, arrString As Variant
Dim lngCount As Long, lngString As Long
Dim blnGood As Boolean

    strString = "test my life"
    arrString = Split(strString, " ")
    lngString = UBound(arrString)
    Set rngSearch = Worksheets(1).Rows("1:100")
    For Each rngRow In rngSearch.Rows
        For lngCount = 0 To lngString
            On Error Resume Next
            blnGood = True
            If rngRow.Find(What:=arrString(lngCount), SearchOrder:=xlByRows).Activate Then
                If Err.Number <> 0 Then
                    blnGood = False
                    Err.Clear
                    Exit For
                End If
            End If
        Next
        If blnGood Then
            Debug.Print rngRow.Row
        End If
    Next
End Sub

Open in new window

0
 
barry houdiniCommented:
Don't know about VBA but you could use Advanced Filter assuming you're looking in a single column. Post back if you're interested...
regards, barry
0
 
mrandersAuthor Commented:
Thanks Barry I am looking in more than one column. Perhaps I can get back to you if no one suggests a VBA solution?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
leonstrykerCommented:
You would need to do this in a loop. Take the string and parse it into an array. Then take the first word of the array and look. Each time you find it, set that row as your second search range and look for all the other words, one at a time. If it does not find one then exist to the top loop.

The top loop needs only to search for the first word, as every word is required.

The other way to do it would be to run a search on every row for each word and if return true, then thats the row you want.

Leon
0
 
mrandersAuthor Commented:
Thanks Leon, I am not an excel vba wizard and was hoping for some code :-)
0
 
leonstrykerCommented:
LOL, and was hoping you are and I would not have to write it.

Ok, can you start it? Can you take a string and parse it into an array?

Leon
0
 
mrandersAuthor Commented:
dim a as variant
dim s as string
..
a=split(s," ")
..

0
 
mrandersAuthor Commented:
Cool! Many thanks!!
0
 
leonstrykerCommented:
You are welcome, and thanks for the grade,

Leon
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.