PDA

View Full Version : Solved: Early loop exit



jwise
08-09-2007, 02:50 PM
Sorry folks. I can't find this anywhere.


For i = 1 to 20
If Cells(3, i) < 0 Then GoTo skip_it
.... ' processing

skip_it:
next i
OR_skip_it:



Hopefully, the above pseudo-code will give you an idea of what I am trying to do. Basically, I want to exit a loop early. Some languages get very upset if you branch outside a loop. The code listed has the disadvantage that it will go through iterations unnecessarily, i.e. once the "< 0" test fails, it will fail always (this is data dependent of course).

1. Could I use OR_skip_it as the destination in the "<0" test, or will VBA get upset?
2. Does vba have an "iterate" statement? This makes your code easier to read, and is probably more efficient.
3. Any restrictions on using the loop variable ("i" in this case) outside the loop?
4. Can I modify "i" inside the loop which alters the number of iterations? Does VBA get upset about this? For example, could I say "i = 21" to cause the immediate termination of the loop?

Thanks in advance

Bob Phillips
08-09-2007, 03:22 PM
For i = 1 To 20
If Cells(3, i) < 0 Then
Exit For
End If
'else do your stuff
Next i

Robert
08-10-2007, 12:06 AM
or you could write as:

For i=1 to 20
If cells(3,i)<0 then Exit for
'else do your stuff
Next

jwise
08-29-2007, 11:49 AM
This was just what I needed. Thanks again.

But it does bring up my gripe... Most folks seem to get a lot more out out of VBA help than I do. Is there some magic phenomena I am missing?

Thanks again.

Bob Phillips
08-29-2007, 11:51 AM
Talent? Skill? Personality? Who knows? :-)

jwise
08-29-2007, 12:21 PM
OK, so you found out I have no talent or skill. And I'm told (by several people) that I have no personality. So what! Did you think I got into the programming business because I was an intelligent socialite?

Bob Phillips
08-29-2007, 12:30 PM
If you were, you would be ostracised by the rest of us, we don't like people who are different.

rbrhodes
08-29-2007, 12:32 PM
The VBA help files can be less than helpful at times. However if you use View/Object Browser and do a search for what you need you can then Right Click on the found item (method) and you sometimes get a totally different set of help files

geekgirlau
08-30-2007, 10:40 PM
I've found the best way to get information out of Help is to already know the name of what you are looking for :confused3

Other than that, our good friends VBAX and Google are your weapons of choice ...