• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 482
  • Last Modified:

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".
0
mranders
Asked:
mranders
  • 4
  • 4
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
leonstrykerCommented:
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
 
mrandersAuthor Commented:
Cool! Many thanks!!
0
 
leonstrykerCommented:
You are welcome, and thanks for the grade,

Leon
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now