Find Method and WholeWord in Excel

[jungix]

Hi guys,

I have a little problem with a macro. I need to find a string in a text, but only if the string is a whole word. The LookAt:=xlWhole is not a solution, as I find an answer only if the string is equal to the whole text.

I saw the WholeWord function in Acess VBA but only with modules, and I don't know how to use it in a macro. Can I? Or is there another way?

[Andy Pope]

Hi,

You probably need to use the xlPart to find a cell and then use further code to validate the find is a whole word and not part of another.

try this
[vba]Sub x()

Dim firstAddress As String
Dim c As Range
Dim vntWords
Dim lngStatus As Long
Dim strSearchWord As String

On Error Resume Next

' A2: =this is my test
' A3: =this is dummy text

strSearchWord = "my"
With Worksheets(3).Range("A:A")
Set c = .Find(strSearchWord, LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
vntWords = Split(c.Value, " ")
lngStatus = 0
lngStatus = Application.Match(strSearchWord, vntWords, 0)
If lngStatus > 0 Then
MsgBox "Found " & strSearchWord & vbLf & "in " & c.Value, vbInformation
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

End Sub[/vba]

[jungix]

Sorry I wasn't clear enough, but I was always looking in one cell. I didn't know the command split. I'll just have to check if lngstatus is <>0 I guess.

Thank you very much.

[mdmackillop]

Hi Jungix,
Welcome to VBAX.
If you don't have punctuation then this should test a selection for valid cells.
Regards
MD
[VBA]
Sub CheckWord()
Dim Test As String, LenTest As Long, Cel As Range
Test = InputBox("Word to find")
LenTest = Len(Test) + 1
Selection.Interior.ColorIndex = xlNone
For Each Cel In Selection
If Left(Cel, LenTest) = Test & " " Or _
InStr(1, Cel, " " & Test & " ") > 1 _
Or Right(Cel, LenTest) = " " & Test Then
Cel.Interior.ColorIndex = 6
End If
Next
End Sub

[/VBA]