VBA INSTR or FIND?

[Trebor8484]

Hi, Can anyone assist with this please.

I am trying to test cells for the below conditions, but I need to find instances where the word VBA or TEST appears in a cell rather than just the string itself, so for example the word TESTING currently shows.

Not sure if I need to use find rather than instr.

For Each c In SrcSheet.Range("H2:H" & SrcSheet.Cells(Rows.count, "A").End(xlUp).Row)

If InStr(1, c.Value, "LESSON", vbTextCompare) > 0 Then

If InStr(1, c.Value, "VBA", vbTextCompare) > 0 And InStr(1, c.Value, "TEST", vbTextCompare) > 0 Then

'Debug.Print c.Value

If rng Is Nothing Then

Set rng = c

Else

Set rng = Union(rng, c)

End If

End If

End If

Next c


Thanks

 

[Dan_W]

I don't know that either will help you here, and neither will the SEARCH function. Most of the solutions online that I've found appear to be predicated on the assumption that there will be a space or some form of punctuation after the given word, but I'm guessing that this will not always be the case (if at all). Is it the case that these words/conditions that you're checking for are not the only word in the cell? If they are, then the solution is pretty simple, but if they're not (and I suspect that they're not) then I actually think that maybe regular expressions might be your best bet.

This page is generally useful, but specifically, the regular expression function coded by Ron Rosenfield here will likely do what you need. In your code above, you would use his code as follows:

If reFindWord(c.Value, "LESSON") Then

Alternatively, if a regular expression is overkill (which is possibly the case), then the following might work (and is perhaps marginally faster), but it may not work with your data. For example, it will return a TRUE value if the word is at the end of a string, if it is in the middle of a string (and then following by a space character and then whatever else), or if there is one other character after it. This third element was added to allow for the possibility of punctuation. So a search for "test" in the following:

• Is this a test? TRUE
• THIS IS A TEST TRUE
• I have a test tomorrow. TRUE
• You're testing my patience FALSE
• I heard that there will be a test!? FALSE

Note that I've made the code case insensitive.

Function ContainExactWord(ByVal strSource As String, ByVal strWord As String) As Boolean
strSource = LCase(strSource)
strWord = LCase(strWord)
strPattern = "*" & strWord
If strSource Like strPattern Or strSource Like strPattern & " *" Or strSource Like strPattern & "?" Then ContainExactWord = True
End Function

 

[Dan_W]

Sorry - I just realised that my function may not work in certain circumstances (namely, where the target word is at the end of another word). I think I've completely overengineered this, and there is doubtless a much simpler solution to your question, but hey ho, here we go. The following is marginally better than my earlier version:

Function ContainExactWord(ByVal strSource As String, ByVal strWord As String) As Boolean
    strSource = LCase(strSource)
    strWord = LCase(strWord)

    If strSource Like strWord _
    Or strSource Like strWord & " *" _
    Or strSource Like strWord & "?" _
    Or strSource Like "* " & strWord _
    Or strSource Like "* " & strWord & " *" _
    Or strSource Like "* " & strWord & "?" _
    Then ContainExactWord = True
End Function

 

[Rick Rothstein]

I believe this function should work correctly...

Function ContainExactWord(Text As String, Word As String) As Boolean
  ContainExactWord = " " & UCase(Text) & " " Like "*[!A-Z]" & UCase(Word) & "[!A-Z]*"
End Function

 

[Dan_W]

As always, a more elegant and simpler approach. Thank you, Rick.

 

[Trebor8484]

Thanks both.

Both solutions work for my specific needs but I went with the one liner.

I spent ages looking for a inbuilt VBA function to accomplish this but didn’t residency I would need a custom function.