Strange (to me) flow control phenomenon

[Sidewinder72]

I have a Do Loop nested in a Do Loop nested in a For Loop (served with fries & cole slaw).

When the condition occurs to exit from the inner Do Loop (Loop #2), I want to exit from the outer Do Loop as well. To accomplish this, I use a Boolean called leave_loop and set it to True when the condition occurs in the inner loop; and the last statement of the outer Do Loop is "Loop While not leave_loop"

(Each row on Sheet1 contains a field with a "Drop Point" whose value is not unique on Sheet1. On Sheet2 is where details associated with each Drop Point (each has its own unique row o Sheet2) are listed, including Origin City. My program so far aims to step through each row on Sheet1, get the Drop Point, and look up its Origin City on Sheet2. Output, to be written to Sheet7, is a list of each city (there can be multiple Drop Points with the same Origin City) with the number of times that city has appeared (via an associated Drop Point) in Sheet 1.)

So there is a For Loop to step through 750 rows on Sheet 1, which
- gets the Drop Point
- goes to Sheet2 and

== enters a Do Loop(#1) which combs Column A for the Drop Point, and gets the Drop Point's city
== then goes to Sheet7 and enters another Do Loop(#2) which

=== Combs Column A for the city, and
=== if it finds it, increments the Send Count in Column B (and sets leave_loop to True)
=== if it doesn't find it (finds an empty cell first), enters the city name in Column A and a Send Count of 1 in Column B (and sets leave_loop to True)
=== since leave_loop is True, we're leaving Loop #1, and the For Loop can continue

Here is the code in the Do Loops. Before the program gets here, drop_point has a valid value, Sheet2 is Activated, and dp_search = 1.

Please Login or Register  to view this content.

Testing the code with the first 10 rows on Sheet1, I have it show a MsgBox each time it scans a Drop Point and each time it scans an Origin City. It would give me the first Drop Point, then its correct city (San Francisco, if that matters ), but then it would only show me each successive Drop Point and not their Origin City values. Sheet7 would show "San Francisco" in Column A and a correct send count of 2 in Column B. So the For Loop on Sheet1 would execute, but the outer Do Loop would execute only for one iteration and not execute again.

I hypothesized that the fact that leave_loop was set to True in the first iteration, and then never set back to False, might have been the problem. I added "leave_loop = False" just before the outer Do Loop is entered, and voilą, that fixed it.

I am confused why that should have fixed it; for the simple reason that leave_loop is to be checked at the end of the Loop, not when the Loop is entered. It's "Loop While not leave_loop"; it's not "Do While not leave_loop".

So why is the value of leave_loop affecting execution at the time the program encounters "Do"?

[6StringJazzer]

There is code you are not showing us, and that is important. You mention a For Loop. If the For Loop surrounds the code you posted here, then that means that the code posted above is going to get executed more than once. The first time it is executed, it runs until leave_loop becomes True. Then it exits the inner loop, and exits the outer loop. Now the For loop iterates again, and it once again enters the outer Do loop. leave_loop is still True. Suppose on the first pass of the outer loop it does not enter the inner loop (because dp2_cell.Value <> drop_point). leave_loop will still be True anyway and it will exit the outer loop. That is why setting leave_loop to False each time fixed it.

A lot of people use this type of flag to signal from one part of code to another, but this is considered control coupling and not a best practice. It is a fine point taught to professional programmers and we don't even all agree on it. But the problem you are having is one of the reasons why it's discouraged. I have a suggestion for a way to restructure your code to make it cleaner, but I am confused by two comments:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The code doesn't do what the comments say. Did you remove code before you posted it? There is a better way to structure this if there is no missing code, but if there is code that goes with this comments that you stripped out then it affects how to restructure it.

[Sidewinder72]

Suppose on the first pass of the outer loop it does not enter the inner loop (because dp2_cell.Value <> drop_point). leave_loop will still be True anyway and it will exit the outer loop. That is why setting leave_loop to False each time fixed it.

Thank you 6String Jazzer, you nailed it (as you know).

(And yes, to keep my post a reasonable length, I removed quite a bit of code, leaving in just a comment or two describing what the excised code does. I meant to explain this in my OP but forgot to; and the "Edit" button seems not to work for me. :-/ )

The excisions obviously still didn't stop you from solving the prob though!! Thanks again!