Skip to next For loop based using If statement

[Mordred]

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.

[Andrew-R]

Please Login or Register  to view this content.

[Andrew-R]

Or, if you're feeling fancy...

Please Login or Register  to view this content.

[davegugg]

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.

[TMS]

A couple of options:

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

[Mordred]

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.

[Mordred]

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

[Mordred]

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!

[TMS]

I'm sure there are one or two purists out there who would argue that "Goto Label" ... as in "Goto Skip" is to be avoided. :-)

[Mordred]

Why avoid it if it works?

[Mordred]

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!

[Andrew-R]

Goto's are messy, amateurish and a sign you haven't thought through your code structure very well. In this case doubly so, because a straightforward If...Then...End If works absolutely fine.

In 15 years of writing Excel macros I have never, with the exception of On Error Goto, needed to use a goto statement.

[Mordred]

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.

[Andrew-R]

Sorry, I wasn't trying to have a go at you personally, but you asked why not use gotos.

[TMS]

In this case, it may seem a "no-brainer" to use a Goto in that there are relatively few lines of code and the Goto and the Label are not greatly separated and it is easy to see. However, as I said, it's a slippery slope. Use it here because it's "easy" and, as *you* said, you'll use it again.

I've been programming for a very long time, not just in VBA, and, IMO, it should be avoided.

Andrew-R's comment may be harsh but, I think, true. You have better, structured coding alternatives that you have rejected.

Anyway, we're off topic and we're clearly not going to agree. Probably not worth continuing in this thread.

Regards

[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. 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.

[Andrew-R]

Being new to VBA is an excellent reason to avoid using goto - as has been noted with a simple macro it's relatively inoffensive and doesn't harm the readability of the code, but the skills and techniques you learn when you're starting out in VBA will be incorporated into larger and more complex macros and you run the risk of ending up with something which is unreadable and unsupportable because it's a maze of spaghetti logic.

Structured, properly indented, code is far easier to read and debug, especially when you're dealing with multi-level nested loops and If statements.

[shg]

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

[broro183]

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

[TMS]

@Mordred

I've just picked up on this thread again because, having gone off topic, I stopped tracking replies.

Being new to VBA within the last 2 months ...

I've been using VBA for 12 years or so but I started working life as a programmer 35+ years ago. I was taught structured programming and to avoid using Goto and I try to apply that. Sometimes, it isn't always easy and it does feel that "the simplest approach" is to use Goto.

And yes, where the Goto is going somewhere relatively local, it can seem less of an issue. However, as others have mentioned, code is often enhanced, improved and extended and, by the time you've got 200 lines of code between the Goto and the Label, it becomes a lot less clear.

I did say purists might not agree :-)

Having said I've been programming a long time, I learn new techniques and approaches every day ... mostly from these forums (fora?) and from following the links they often provide.

Kind regards

[Mordred]

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

[Andrew-R]

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

[romperstomper]

Please Login or Register  to view this content.

[romperstomper]

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.

[Andrew-R]

Ah, somebody else read that as well. I must say that nothing in it really convinced me that there's any place for gotos in structured programming. The example he gives of trying to solve the 8 queens problem without using gotos seems particularly flawed.

[Mordred]

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

[Andrew-R]

As a general rule you only need to use Select if you want to direct the user to the specified cell/range, although sometimes it's easier to use it when you're pasting data.

So each time you add a select ask yourself, "Does the user need to see this?". If you find yourself using select and then, say, a For ... Each ... Next statment using the Selection object look at combining the two actions. So:

Please Login or Register  to view this content.

Becomes:

Please Login or Register  to view this content.

It saves your code dotting all over the place and means that, in most cases, you don't need to worry about turning screenupdating on and off (there's no better way to spend 5 minutes than watching a macro do your work for you anyway)