How To Use VBA .Find fuction to find an EXACT match

[markronz]

Hello everyone-
I really hope that there is a way to do this. I am trying to code some VBA that will search for a word and then add it to a dictionary once it is found. I was able to accomplish this, however, I need my search to be more exact. Right now if I search for the word "test" it will find words like "testing" and "detest". What I would like it to do is only give me the results that have the exact word "test". Here is my code that I have now:

Dim wordDictionary
Set wordDictionary = CreateObject("Scripting.Dictionary")

searhWord = "test"

Set searchResult = MySearchArea.Find(searchWord, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)

If searchResult Is Nothing Then
            MsgBox "Did not find anything for searchword: " & searchWord
Else
     wordDictionary.Add Str(searchResult.Value)
End If

Does anyone know how to search for an exact match like that? Also a certain cell may have the whole phrase "This is a test" in it, but that is a valid match since the word "test" is alone. What I'm trying to say is that the cell will not just have the word "test" in it, it would have a whole sentence. That's hard to explain, hopefully that makes sense.

Anyone have any ideas? Thanks in advance!

-Mark

 

[SkipVought]

Hi,

Dim wordDictionary
Set wordDictionary = CreateObject("Scripting.Dictionary")

searhWord = "test"

Set searchResult = MySearchArea.Find(searchWord, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)

If searchResult Is Nothing Then
            MsgBox "Did not find anything for searchword: " & searchWord
Else[b]
  if searchWord = searchResult.value then _
[/b]
     wordDictionary.Add Str(searchResult.Value)
End If

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[markronz]

Well, searchResult.value would be the entire line that is in the cell. I was trying to explain above that my cell might actually contain an entire sentence, such as "this is a test" and that is a valid match. so searchWord = searchResult.value would not work for me. I somehow need to ignore the rest of the sentence and focus on the "test" portion of the cell and check around that word. Like if it says "detest" it would notice there is a "de" before the word "test" and it wouldn't count as a match. Any other ideas?

 

[xlbo]

You will need to test the cell that is found using INSTR to find the postition of tyhe matching text. You then need to look at the character before and the character after the text and test that they are any valid character - ie a space or a full stop. Anything other than that and it is a mis-match

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fumei]

Why not just search for " test "? If you want to search for "test" as a only a word, then search for it as a word.

To cover issues like " test.", you could search for " test" - no trailing space - then check the next character.

Though, essentially, you need to do just as xlbo states.

Find it, and then check the characters preceding, and after.

faq219-2884

Gerry
My paintings and sculpture