Do While Loop with multiple conditions not working.

[Zabman]

Hi,

I have written the following code, expecting this to loop down from the cell named "TT_Unload", until all 3 conditions are met, however this is exiting the loop straight away.

            Do While Range("TT_Unload").Offset(iLookup) <> "" And _
                Range("TT_Unload").Offset(iLookup) <> Range("Report_Unload").Offset(iCount) And _
                Range("TT_Unload").Offset(iLookup, 1) <> Range("Report_Dest").Offset(iCount)
                
                iLookup = iLookup + 1
            Loop

Can anybody help? I have been trying to get to the bottom of this for the last 3 hours!

 

[mikerickson]

The first thing I would do is see if specifying the missing Offset arguments helped.

It will stop if
Range("TT_Unload").Offset(iLookup) = ""
or
Range("TT_Unload").Offset(iLookup) = Range("Report_Unload").Offset(iCount)
or
Range("TT_Unload").Offset(iLookup, 1) = Range("Report_Dest").Offset(iCount)

How many cells are in those named ranges? Are any of them multi-cellular?
What data is in those four cells?

 

[tusharm]

Put in a breakpoint and check the values of the different variables and ranges. See what's what.

Hi,

I have written the following code, expecting this to loop down from the cell named "TT_Unload", until all 3 conditions are met, however this is exiting the loop straight away.

            Do While Range("TT_Unload").Offset(iLookup) <> "" And _
                Range("TT_Unload").Offset(iLookup) <> Range("Report_Unload").Offset(iCount) And _
                Range("TT_Unload").Offset(iLookup, 1) <> Range("Report_Dest").Offset(iCount)
                
                iLookup = iLookup + 1
            Loop

Can anybody help? I have been trying to get to the bottom of this for the last 3 hours!

 

[Zabman]

Hi,

I have a breakpoint at this point as I was trying to troubleshoot this for a couple of hours yesterday. All of the named ranges are single cells, and not merged in anyway. The data in the cells is a 3 letter code.

I have checked the value of the ranges when it exits the loop (by holding the cursor over the range in breakmode), and 2 of the conditions are not met, but one is.

Mike - Are you saying that it will exit the loop if even one of your conditions is true? That looks like it is the problem then. How do I make it so it continues to loop until all 3 conditions are met? I want it to loop down from Range("TT_Unload") until these conditions are true:

Range("TT_Unload").Offset(iLookup) = Range("Report_Unload").Offset(iCount)

Range("TT_Unload").Offset(iLookup, 1) = Range("Report_Dest").Offset(iCount)

But exit the loop if it gets to an empty cell without both of the above conditions being met.

I hope this makes sense.

 

[tusharm]

A good way to learn is to use only 2 conditions, say C1 and C2.

Clearly C1 and C2 can have 4 possible combinations
True, True,
True, False
False, True
False, False

Now, if you have "do while C1 and C2" what will happen for each of the above combinations?

What will happen if you have "do until"?

What will happen if you replace the 'and' with 'or'?

Once you understand this adding more conditions is not difficult at all.

Hi,

I have a breakpoint at this point as I was trying to troubleshoot this for a couple of hours yesterday. All of the named ranges are single cells, and not merged in anyway. The data in the cells is a 3 letter code.

I have checked the value of the ranges when it exits the loop (by holding the cursor over the range in breakmode), and 2 of the conditions are not met, but one is.

Mike - Are you saying that it will exit the loop if even one of your conditions is true? That looks like it is the problem then. How do I make it so it continues to loop until all 3 conditions are met? I want it to loop down from Range("TT_Unload") until these conditions are true:

Range("TT_Unload").Offset(iLookup) = Range("Report_Unload").Offset(iCount)

Range("TT_Unload").Offset(iLookup, 1) = Range("Report_Dest").Offset(iCount)

But exit the loop if it gets to an empty cell without both of the above conditions being met.

I hope this makes sense.