VBA - Trying to find my error

[nick1408]

I have the following code:

Debug.Print InStr(1, ActiveCell.Offset(0, 14).Value, "1", vbTextCompare) = 0
Debug.Print InStr(1, ActiveCell.Offset(0, 14).Value, "2", vbTextCompare) = 0
Debug.Print InStr(1, ActiveCell.Offset(0, 2).Value, "3", vbTextCompare) = 0
Debug.Print InStr(1, ActiveCell.Offset(0, 2).Value, "4", vbTextCompare) = 0
Debug.Print InStr(1, ActiveCell.Offset(0, 2).Value, "5", vbTextCompare) = 0
Debug.Print InStr(1, ActiveCell.Offset(0, 2).Value, "6", vbTextCompare) = 0
Debug.Print InStr(1, ActiveCell.Offset(0, 2).Value, "7", vbTextCompare) = 0
Debug.Print InStr(1, ActiveCell.Offset(0, 2).Value, "8", vbTextCompare) = 0
Debug.Print InStr(1, ActiveCell.Offset(0, 5).Value, 999, vbTextCompare) = 0

Debug.Print InStr(1, ActiveCell.Offset(0, 14).Value, "1", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 14).Value, "2", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "3", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "4", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "5", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "6", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "7", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "8", vbTextCompare) = 0 _
And InStr(1, ActiveCell.Offset(0, 5).Value, 999, vbTextCompare) = 0

Essentially this is an IF statement that I am trying to get to work but I am debugging to see where I am failing. What I want to work out is if all lines are TRUE except the last line why does the bottom statement come out as true? I would have expected a FALSE return if activecell.offset(0,5).value does not equal 999

 

[Rick Rothstein]

Debug.Print InStr(1, ActiveCell.Offset(0, 14).Value, "1", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 14).Value, "2", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "3", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "4", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "5", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "6", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "7", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "8", vbTextCompare) = 0 _
And InStr(1, ActiveCell.Offset(0, 5).Value, 999, vbTextCompare) = 0

Essentially this is an IF statement that I am trying to get to work but I am debugging to see where I am failing. What I want to work out is if all lines are TRUE except the last line why does the bottom statement come out as true? I would have expected a FALSE return if activecell.offset(0,5).value does not equal 999

The And operator has precedence over the Or operator, so it "binds" with the next to last line to produce effectively what this code shows below (note the red parentheses)...

Debug.Print InStr(1, ActiveCell.Offset(0, 14).Value, "1", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 14).Value, "2", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "3", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "4", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "5", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "6", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "7", vbTextCompare) = 0 _
Or (InStr(1, ActiveCell.Offset(0, 2).Value, "8", vbTextCompare) = 0 _
And InStr(1, ActiveCell.Offset(0, 5).Value, 999, vbTextCompare) = 0

what you need is this which forces the last line to stand alone against all of the Or'ed line of code (again, note the red parentheses)...

Debug.Print (InStr(1, ActiveCell.Offset(0, 14).Value, "1", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 14).Value, "2", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "3", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "4", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "5", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "6", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "7", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "8", vbTextCompare) = 0) _
And InStr(1, ActiveCell.Offset(0, 5).Value, 999, vbTextCompare) = 0

 

[nick1408]

Thanks heaps! All makes sense!

 

[nick1408]

So, would a better structure be:

Debug.Print InStr(1, ActiveCell.Offset(0, 5).Value, 999, vbTextCompare) <> 0 _
And (InStr(1, ActiveCell.Offset(0, 14).Value, "1", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 14).Value, "2", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "3", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "4", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "5", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "6", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "7", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "8", vbTextCompare) = 0)

 

[Rick Rothstein]

So, would a better structure be:

Debug.Print InStr(1, ActiveCell.Offset(0, 5).Value, 999, vbTextCompare) <> 0 _
And (InStr(1, ActiveCell.Offset(0, 14).Value, "1", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 14).Value, "2", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "3", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "4", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "5", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "6", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "7", vbTextCompare) = 0 _
Or InStr(1, ActiveCell.Offset(0, 2).Value, "8", vbTextCompare) = 0)

It makes no difference. If you think you can visualize the relationship more easily, though, then go with it.