OR function does not work in Do while loop

[Jurg55]

Hi,

Why does the Or-function not work here?

Dim j As Integer
j = 3
Do While Cells(j, 10).Value = “001” Or “002”
Cells(j, 2).Value = “400”
j = j + 1
Loop

Column 10 looks like the following:
001
002
101
102
103
So column 2 should look like the following:
400
400
‘Empty’
‘Empty’
‘Empty’
It should stop at 101

But column 2 looks now like the following and that is wrong:
400
400
400
400
400
400
Till infinity
Excel freezes and crashes

Site note: the cells are all ‘text’-formattypes.
So 001 is text not a number. (Selection.NumberFormat=“@“)

Please help me
Thank you!

 

[Joe4]

Each part of an OR statement needs to be a complete check/comparison, i.e.

Do While (Cells(j, 10).Value = "001") Or (Cells(j, 10).Value = "002")

 

[Jurg55]

Oke thank you!

and what if i want to check it from 001 till 099 with or functions between them.
So first row check for 001 till 099
Seconde row check for 001 till 099
And so on

I dont want to type 99 times cells().value ofcourse.

 

[Joe4]

What other possible values can you have, outside of 001- 099?
Are they are numbers, or can there be any letters or symbols or spaces in there?

If they are all numbers, how about converting them to numbers, and seeing if they are greater than or equal to 1, and less than or equal to 99?
Your can convert the value in Cell(j, 10) to a number by multiplying by 1, i.e.

Cells(j ,10)*1

 

[Jurg55]

What other possible values can you have, outside of 001- 099?
Are they are numbers, or can there be any letters or symbols or spaces in there?

If they are all numbers, how about converting them to numbers, and seeing if they are greater than or equal to 1, and less than or equal to 99?
Your can convert the value in Cell(j, 10) to a number by multiplying by 1, i.e.

Cells(j ,10)*1

I needed text and not numbers. But thank you!
I solved it. It had to use AND.
And changed the conditions to <>”100” AND <>”” so it will stop at 100 or empty.
Still strange that I needed to use AND instead of OR to get it work. I was expecting that the loop would do while the cell <> “100” OR <> “”.
But now it worked with AND.

 

[Joe4]

I needed text and not numbers.

I was not suggesting changing the data. I was suggesting how VBA interacts with the data by converting to a number for comparison sake in the VBA code only.

And changed the conditions to <>”100” AND <>”” so it will stop at 100 or empty.

That won't "stop" at 100. It just checks for the value not equal to 100. If you have other values like "101", "102", etc., it won't catch those.

Still strange that I needed to use AND instead of OR to get it work. I was expecting that the loop would do while the cell <> “100” OR <> “”.
But now it worked with AND.

AND makes perfect sense. It is because you are doing NOT equal to. If you had used OR, it will ALWAYS return TRUE.

Think about it closely. Any value you pick will either be NOT equal to "100" or NOT blank, because it is impossible to be both "100" and "blank" at the same time!
So an expression like:

cell <> “100” OR  cell <> “”

can NEVER be FALSE! It will always return TRUE, no matter what value cell is.
Using not equal to "<>" and OR confuses a lot of people!