Using not equal to on IF statement

[Blues Clues]

Hello, I've been researching for a while but haven't been able to find an explanation. I know the issue is with the "not equal to" part but I don't know why.

Why does the code updates the XYZ cell on column B when the cells on column C are "CHECKED" or "REVISED". The intention with the code is for it to not update the field when cells on col C are CHECKED or REVISED.

For i = 2 To LastRow
If Range("B" & i).Value = "XYZ" And (Range("C" & i).Value <> "CHECKED" Or Range("C" & i).Value <> "REVISED") Then
Range("C" & i).Value = "PENDING"
Else
...

 

[shg]

(Range("C" & i).Value <> "CHECKED" Or Range("C" & i).Value <> "REVISED")

That's always true -- your name may be Bob, or Joe, or something else, but it's definitely either not Bob or not Joe.

 

[Blues Clues]

Hi shg,

But if the cell on col C = CHECKED, wouldn't that stop the IF condition, i.e., not update the cell on col B to PENDING? Following the logic of TRUE OR FALSE = TRUE.

 

[shg]

If you explain in words the result you're trying to achieve, I'll give you the Boolean expression that implements it.

 

[Tetra201]

... The intention with the code is for it to not update the field when cells on col C are CHECKED or REVISED...

Literal translation:

... And [COLOR=#ff0000]Not[/COLOR] (Range("C" & i).Value [COLOR=#ff0000]=[/COLOR] "CHECKED" [COLOR=#ff0000]Or[/COLOR] Range("C" & i).Value [COLOR=#ff0000]=[/COLOR] "REVISED") Then...

Equivalent with <>:

... And (Range("C" & i).Value [COLOR=#ff0000]<>[/COLOR] "CHECKED" [COLOR=#ff0000]And[/COLOR] Range("C" & i).Value [COLOR=#ff0000]<>[/COLOR] "REVISED") Then...

 

[joeu2004]

If Range("B" & i).Value = "XYZ" And (Range("C" & i).Value <> "CHECKED" Or Range("C" & i).Value <> "REVISED") Then

But if the cell on col C = CHECKED, wouldn't that stop the IF condition, i.e., not update the cell on col B to PENDING? Following the logic of TRUE OR FALSE = TRUE.

First, VBA does not stop left-to-right calculations when a left-hand calculation might preclude the need for the right-hand calculations.

For example, even if Range("B" & i)="XYZ" is false, the right-hand expression is still evaluated even though the AND is already false.

But that is neither here nor there.

You are right: TRUE or FALSE = TRUE. More to the point, FALSE or TRUE = TRUE. And that was shg's point.

If Range("C" & i)<>"CHECKED" is true, the OR operation returns True.

But if Range("C" & i)<>"CHECKED" if false, then Range("C" & i)="CHECKED". Ergo, Range("C" & i)<>"REVISED" is true, and the OR operation returns True again.

Since Range("C" & i)<>"CHECKED" must be either true or false, we have just demonstrated that the OR operation always returns True.

I suspect the logic that you want is: set Range("C" & i) to "PENDING" if Range("B" & i) is "XYZ" and Range("C" & i) is neither "CHECKED" nor "REVISED". That is:

If Range("B" & i).Value = "XYZ" And Range("C" & i).Value <> "CHECKED" And Range("C" & i).Value <> "REVISED" Then