[SOLVED] FOR, IF, NEXT... Error in VBA while attempting a simple code.

mewingkitty · #1 01-09-2009, 05:50 PM

Me again, still plugging away at VBA.
Had a bit of code I was working on for a guy on here, and came across a problem while putting the finishing touches on it. It's since been solved by someone else, but for future reference...

Code:

Dim iFor As Integer

For iFor = 1 To 10

    If Cells(1, 1) = 1 Then
    Next iFor
    Else
    Cells(1, 2) = 2
    End If
    
Next iFor
    
End Sub

**Leith Ross** · #2 01-09-2009, 05:59 PM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Sincerely,
Leith Ross

mewingkitty · #3 01-09-2009, 06:19 PM

I sometimes forget that although I'm fun and amusing, my thread titles are not allowed the same luxury for the sake of the search engine. Thanks for not hitting me with an infraction.

At any rate, I've been messing with this for a better part of the day, and searching the forum, and the VBA help files. Can't seem to find the format in which you use NEXT to loop back to a FOR statement within an IF.

mew?

stanleydgromjr · #4 01-09-2009, 06:38 PM

mewingkitty,

Try:

Code:

Option Explicit
Sub Test()

    Dim iFor As Integer
    For iFor = 1 To 10
        If Cells(1, 1) <> 1 Then
            Cells(1, 2) = 2
        End If
    Next iFor

End Sub

mewingkitty · #5 01-09-2009, 06:42 PM

I had a much longer code with several IF's which, if any of them came back as true, I would want to terminate all following if statements, and proceed with the next NEXT part of the loop.

My reasoning behind using this simplistic example was to save time for anyone looking at it, and make it easier for anyone else with the same question to understand if they found it later.

So...
How do you loop back to a NEXT if a condition is met half way through the code?

**mudraker** · #6 01-09-2009, 06:45 PM

mewingkitty

You can only have 1 Next command for each For command. - Your code has 2 Next commands.

This limit also applies to End If's, End Selects, Do & Wend loops

mewingkitty · #7 01-09-2009, 06:54 PM

Alrighty then.
Thanks!

broro183 · #8 01-09-2009, 07:21 PM

As only a single Next statement can be used, you can carefully structure the logic tests using If or Select Case statements so that as soon as one condition is met, the "next line of processed code" becomes the Next line. However, if you can't easily do this, place a Label immediately before the Next line & use goto statements, for example

Code:

if xyz = abc then goto Continue
range(123).value = 5
if uvw = def then goto Continue
'...
Continue:
next ifor

mewingkitty · #9 01-10-2009, 01:55 PM

Thanks, that's what I was looking for.

Had a list of conditions specific to a particular sheet, and the criteria were somewhat... sketchy, so I wanted an easy out on the IF statements :D

broro183 · #10 01-10-2009, 06:45 PM

Beauty, pleased I could help :-)

btw, you may be able to get your code to run much quicker (w/o having seen it) if you move some or all your conditions out of the code & (depending on actual details) put them into a single boolean helper column &/or then limit your range to be looped through using something like Advanced Filter/AutoFilter/xlspecialcells.
If you want someone to glance over your code for potential optimisation you could post a sample file in a new thread (I reckon this one's done & dusted now + you've marked it as solved).

Rob

shg · #11 01-10-2009, 08:21 PM

I'll take a little issue with Rob's suggestion. Using GoTo statements for anything other than error handling make code hard to follow, debug, and maintain, and VBA has a wealth of flow control structures to choose from to avoid them. For example,

Code:

    For i = 1 To 10
        If cond1 Then
            ' do this
        ElseIf cond2 Then
            ' do that
        Else
            ' do the other thing
        End If
    Next i

**mudraker** · #12 01-10-2009, 08:50 PM

I agree with shg that it is best to avoid using Goto's except for error handling

Another option is to built several smaller macros that each do part of what you require and call them from a main macro

Using shg code as an example

Code:

    For i = 1 To 10
        If cond1 Then
            call macro1
        ElseIf cond2 Then
            call macro2
        Else
          call macro3
        End If
       if newCondTest then
          call macro4
       end if
    End If

broro183 · #13 01-11-2009, 04:56 AM

Ooopps!

Yes, my feelings are the same as Shg's & Mudraker's.
I should have put more emphasis on my statement "you can carefully structure the logic tests using If or Select Case statements" (or "smaller macros" as Mudraker suggests) rather than just providing the easy "spaghetti coding" but less structured coding option.

Rob

mewingkitty · #14 01-12-2009, 03:07 PM

Thanks for all the feedback, I certainly want to learn good practice early on so I don't pick up bad habits as I'm learning all of this. I truly appreciate the open minded feedback from y'all!

mew!

Welcome to the Excel Forum

Forum Statistics