• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

use .find "LookAt:=xlPart" but match whole word

mdavid

Member
Hi, I have a list of medical complaints which I'm searching for in phrases using
Code:
rngSearch.Find(What:=medicalComplaint, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
But I need to find whole words so for example if the phrase contains "heartburn" I don't want to find "burn". How do I ensure that LookAt:=xlPart only returns whole words?
Thanks for any help
 

p45cal

Well-Known Member
Excel isn't good at looking for word boundaries; Marc L 's suggestion will find matches but will miss the word at the beginning of the cell's value, and still find (using his example of space before the word) burner, burned etc. So you will have to make multiple checks or…
Consider using regular expressions which know about word boundaries.
If you want, I'll write a snippet of code that will do this but I do need a bit more than a single line of code; supply some more code-context and I'll integrate it into that code (preferably, the whole Sub). Are you searching a very big range for these words?
 

mdavid

Member
Hi p45cal,
Thanks very much for your explanation. I'm searching for medical conditions/complaints in phrases so for example if the search term is "gas" the search is returning "Gastro-intestinal inflammation" or for "burn" is returning heartburn. Here's the code I'm using:

Code:
Sub findNextComplaint(searchItem, lastRow, totRows)
' searchItem: term to search for in phrase.
Dim rngSearch As Range, rngLast As Range, Found As Range
Dim strFirstAddress As String
Dim firstFound As Long
Set rngSearch = Sheets(2).Range("D2:D" & totRows)
Set rngLast = rngSearch.Cells(rngSearch.Cells.Count)

' search for term - using LookAt:xlPart doesn't recognize word bounderies
Set Found = rngSearch.Find(What:=searchItem, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not Found Is Nothing Then
    firstFound = Found.Row
    Do
        Set Found = rngSearch.FindNext(Found)
        If Not Found Is Nothing Then
            Sheets(4).Cells(lastRow - 1, 3).Value = Found.Value
            lastRow = lastRow + 1
            Sheets(4).Range("A" & lastRow).EntireRow.Insert
        End If
    Loop Until Found.Row = firstFound
End If
End Sub
I would very much appreciate any help to improve the search by limiting it to whole words
 

p45cal

Well-Known Member
try:
Code:
Sub findNextComplaint(searchItem, lastRow, totRows)
' searchItem: term to search for in phrase.
Dim rngSearch As Range, rngLast As Range, Found As Range
Dim strFirstAddress As String
Dim firstFound As Long
Set rngSearch = Sheets(2).Range("D2:D" & totRows)
Set rngLast = rngSearch.Cells(rngSearch.Cells.Count)

'regular expression setup:
Set regex = CreateObject("vbscript.regexp")
regex.ignorecase = True
regex.MultiLine = True
regex.Pattern = "\b" & searchItem & "\b"

' search for term - using LookAt:xlPart doesn't recognize word bounderies
Set Found = rngSearch.Find(What:=searchItem, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not Found Is Nothing Then
  firstFound = Found.Row
  Do
    Set Found = rngSearch.FindNext(Found)
    If Not Found Is Nothing Then
      If regex.test(Found.Value) Then 'added line
        Sheets(4).Cells(lastRow - 1, 3).Value = Found.Value
        lastRow = lastRow + 1
        Sheets(4).Range("A" & lastRow).EntireRow.Insert
      End If 'added line
    End If
  Loop Until Found.Row = firstFound
End If
End Sub
 

mdavid

Member
Hi p45cal,
Thanks very much for this - works perfect!
Going straight into my code snippet treasure chest.
Think these forums are one of the few places these days where you can still find altruism.
Much appreciated
 
Top