InStr function that tests for positive and negative matches.

I need a code that will test for the presence of the word "recovered" in an offset range of cells (Column T) and clear the original target cell contents (Column AD) , but only if "recovered" is NOT preceded by a "no" or "not".  I never know whether the words will occur upper, lower, or proper case.

The code I have below doesn't work consistently.

Thanks,
John
Sub ClearSome()
Dim cel As Range
For Each cel In Range("AD6:AD469")
If InStr(LCase(cel.Offset(0, -10)), "not recover") Or InStr(LCase(cel.Offset(0, -10)), "no recover") = 0 And InStr(LCase(cel.Offset(0, -10)), "recover") > 0 Then
    'cel.ClearContents
    cel.Interior.ColorIndex = 40

Else: End If
Next
End Sub

Open in new window

LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
 
zorvek (Kevin Jones)ConsultantCommented:
Sub ClearSome()
Dim cel As Range
For Each cel In Range("AD6:AD469")
If Not (InStr(LCase(cel.Offset(0, -10)), "not recover") > 0 Or InStr(LCase(cel.Offset(0, -10)), "no recover") > 0) And InStr(LCase(cel.Offset(0, -10)), "recover") > 0 Then
    'cel.ClearContents
    cel.Interior.ColorIndex = 40

Else: End If
Next
End Sub

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Simpler:

Sub ClearSome()
Dim cel As Range
For Each cel In Range("AD6:AD469")
If InStr(LCase(cel.Offset(0, -10)), "not recover") = 0 And InStr(LCase(cel.Offset(0, -10)), "no recover") = 0 And InStr(LCase(cel.Offset(0, -10)), "recover") > 0 Then
    'cel.ClearContents
    cel.Interior.ColorIndex = 40

Else: End If
Next
End Sub
0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Amen. Thanks Kevin. I did have to change the first "And" to "Or" in the second one, but hey, even savants make the occasional typo :-)

- John
0
 
zorvek (Kevin Jones)ConsultantCommented:
No no no!

In the second version the logic changes!

When the compiler parses logic, the AND operator has precedence over OR. So my version:

If InStr(LCase(cel.Offset(0, -10)), "not recover") = 0 And InStr(LCase(cel.Offset(0, -10)), "no recover") = 0 And InStr(LCase(cel.Offset(0, -10)), "recover") > 0 Then

becomes:

If InStr(LCase(cel.Offset(0, -10)), "not recover") = 0 Or InStr(LCase(cel.Offset(0, -10)), "no recover") = 0 And InStr(LCase(cel.Offset(0, -10)), "recover") > 0 Then

which is evaluated as:

If InStr(LCase(cel.Offset(0, -10)), "not recover") = 0 Or (InStr(LCase(cel.Offset(0, -10)), "no recover") = 0 And InStr(LCase(cel.Offset(0, -10)), "recover") > 0) Then

Which is NOT what you want!

The original states that the condition is true only if "not recover" IS NOT in the string AND "no recover" IS NOT in the string AND "recover" IS in the string. This is what you want.

Your "fixed" version states that the condition is true only if "not recover" IS NOT in the string OR BOTH "no recover" IS NOT in the string AND "recover" IS in the string. This is NOT what you want as any string will now produce a positive result. For example, the following strings will all produce a positive result:

"not recover"
"no recover"
"recover"
""
"Kevin is a freaking genius!"

Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.