Skip to next For loop based using If statement

If I have a loop like:

Please Login or Register  to view this content.

What do I do if I set a criteria to the loop where if it is true, move onto the next c like this:

Please Login or Register  to view this content.

I've tried a couple of combinations like:

??? = Next C - doesn't work
??? = Next For - doesn't work

I'm sure it can be done but I don't know the syntax.

You could change the "=" to "<>" and make all the other code a condition in the if statement like this:

Please Login or Register  to view this content.

Or you could tell it to skip to the line right above the next c with a line label.

Please Login or Register  to view this content.

Ok, maybe I was too vague there was that it Andrew?
I'm going to give the whole sub:

Please Login or Register  to view this content.

Alright I'll try what you have suggested, you got your posts up before me so disregard the last one. Thanks.

davegugg has the winning code but thanks to all that helped. Although, me thinks Andrew was being a little cheeky

This is what I used:

Please Login or Register  to view this content.

Again thank you!

Why avoid it if it works?

I see, so then its not really "cool" to use it so don't if you want to be "cool"?!

The way I see it, if its not broke then don't fix it. It worked like a charm for me and if I have to use it again to bust out of a For Each loop, I probably will.

No Doot Aboot it!

I don't understand how Goto, at least in my case is messy? As far as mess goes, there is far less code and hence mess using GoTo Skip than If....Then.....End If. From what I can gather, GoTo is an agreed apon preference, or lack there of, for experienced coders. I'm thinking it is probably because it is an easy way out of a loop that gets you experienced coders frazzled by something like GoTo. Either way, to say it is

a sign you haven't thought through your code structure very well

is kind of harsh isn't it? I am constantly thinking through the code structure. So far, with the help of this forum and my own thinking, I've been able to finish 4 projects this summer for work where they are 98% fully automated. While this may not impress experienced coders, I take great pride in a completed job that for the most part, I did. This last project has stumped me for a week and the last thing that prevented completion came down to a method that is

amateurish and a sign you haven't thought through your code structure very well

Not very cool.

Its not solely that I have rejected other alternatives. Being new to VBA within the last 2 months, I don't understand a fraction of the capabilities of VBA. I also don't know what kind of code is frowned upon as I am learning as I go. If I understand you correctly TM, its should be avoided because of visibility within code, especially if you have a lot of code right? By the way, I did say

if I have to use it again

I will, but I have a lot of If statements in other subs where I get out of Do While Loops if required. I appreciate your input on the matter.

Goto's are messy, amateurish and a sign you haven't thought through your code structure very well. ... In 15 years of writing Excel macros I have never, with the exception of On Error Goto, needed to use a goto statement.

Never is a very long time. Interested readers will google Structured Programming with go to Statements by Donald Knuth

Originally Posted by Mordred

Its not solely that I have rejected other alternatives. Being new to VBA within the last 2 months, I don't understand a fraction of the capabilities of VBA. I also don't know what kind of code is frowned upon as I am learning as I go...

hi Mordred,
I've just come across this thread via "I need a macro to do this..." with the discussion on "good coding & personal biases" etc. This is to provide a link for your learning rather than restart the previous discussion.
I've got about 4 years of VBA here & there, under my belt now and I'm still "learning as I go too" :-)

If you want to absorb a lot of advice & read a wide range of views on various coding approaches I suggest you goto http://www.excelforum.com/excel-prog...id-in-vba.html

Rob

So I finally decided, just now, to figure out a way to move the next iteration of the For Each Loop, without using the GoTo Label. I haven't had a chance because of other work and such but today we did an update where "*" changed to a "." to fill an empty cell. Because of having to figure out how to avoid a run time error because of that, I figured I would get rid of the Goto Label (if I could). Here is the code so this can be officially solved.

Please Login or Register  to view this content.

Thanks again for all of your help and the nudge to get past a GoTo.

Best Regards

Your loop construction is a little strange - it loops while bln<>FALSE, but the final line of code within the loop forces it to exit the loop if bln <> FALSE, so it can only possibly execute the code within the loop 0 or 1 times. In such circumstances it would be more straightforward to use an IF ... THEN ... END IF construction, e.g.

Please Login or Register  to view this content.

This is especially the case as nothing within the loop changes the value of bln. Also, you're checking that rng is set to a valid range at the start of the block of code above, but at the end you pastespecial to it without again checking that it's valid. If there's the possibility that rng won't be set to a valid range then your code probably needs to be;

Please Login or Register  to view this content.

Edited to add: And, just to be really picky, you've got a few select statements in there which could be chopped

Please Login or Register  to view this content.

Originally Posted by shg

Never is a very long time. Interested readers will google Structured Programming with go to Statements by Donald Knuth

My favourite bits:

Most go to's shouldn’t be there in the first place! What we really want is to conceive of our program in such a way that we rarely even think about go to statements, because the real need for them hardly ever arises.

premature optimization is the root of all evil

and

undisciplined go to statements make program structure harder to perceive, and they are often symptoms of a poor conceptual formulation. But there has been far too much emphasis on go to elimination instead of on the really important issues; people have a natural tendency to set up an easily understood quantitative goal like the abolition of jumps, instead of working directly for a qualitative goal like good program structure.

Thanks guys. I appreciate your inputs. I whipped that last code together fast and only checked that it worked once. @Andrew, I thought it looked a little off too, and a little cluttered. You masters once again put me on the right path. I'm still trying to work out different ways to write code without using .Select so much. I started going through previous code a few days ago but there's a lot to go through. Again, thank you.

Best Regards