Need help modifying VBA function to search only complete words.

[Invisible Hand]

Hey guys,

I've been working on a method to search a single column of 1000+ cells containing a paragraph with a 250+ word keyword list (also in a single column). I'm needing only complete words to be searched/ displayed regardless of spacing & punctuation. I've tried multiple methods the past few days and the one that worked (Macro with 4 complex formulas) was unusable when scaled out to all 1,000 rows due to the extremely long calculating time. I don't know VBA yet but was able to modify and create a user defined function that does everything I need except it displays string results that are not whole words (Ex. search for air, word in cell is fair, displays a result for air).

If anyone can help me add conditions to my code that search and return only complete words I'd greatly appreciate it.

Function RangeSearch2(text As String, wordlist As Range, seperator As String, Optional caseSensitive As Boolean = False)
    Dim strMatches As String
    Dim res As Variant
    Dim arrWords() As String
    Dim skip As Boolean
    skip = False
    On Error Resume Next
    Err.Clear
    For Each word In wordlist
            If caseSensitive = False Then
                res = InStr(LCase(text), LCase(word))
            Else
                res = InStr(text, word)
            End If
            If res > 0 Then
                strMatches = strMatches & seperator & word
            End If
    Next word
     If Len(strMatches) <> 0 Then
        strMatches = Right(strMatches, Len(strMatches) - Len(seperator))
    End If
    RangeSearch = strMatches
End Function  

[HaHoBe]

Hi, Invisible Hand,

InStr will always return any partial match as it will check if that part is anywhere in the searched string so that UDF should turn out not to be what you want. IŽd guess you would have to use Find/FindNext and xlWhole for a match.

CIao,
Holger

[Invisible Hand]

Hi, Invisible Hand,

InStr will always return any partial match as it will check if that part is anywhere in the searched string so that UDF should turn out not to be what you want. IŽd guess you would have to use Find/FindNext and xlWhole for a match.

CIao,
Holger

Crap. I guess I wasted another 2 hours on this.

I appreciate the heads up though. I wont waste any more time trying to embed the UDF into an IF function to filer out unwanted results LOL. I wonder how quick the paid help on here is.

Thanks for the reply,

-Jared

[Invisible Hand]

Got it working by altering the word list portion.

Now all I need is macro that will do the following:
-run from a button that is copied to 1000+ rows
-highlight individual words in found a cell that match words in another cell on the same row
-uses the location of the button to identify which cells to be searched and referenced (since the button will be used on multiple rows)
-opens the now searched and highlighted cell as if being double clicked to display its entire contents.

Any advice as to where to start would be appreciated. I've been playing around with different methods for a while now.

[HaHoBe]

Hi, Invisible Hand,

maybe use either Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) or Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) behind the sheet to trigger the search, Both will deliver the row number as Target.Row and could be used instead of copying buttons. Narrow the area where to work down by chekcing via Intersect and use Cancel = True to disable the normal behaviour for the event.

Ciao,
Holger