Welcome to the Excel Forum

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed.

Please Register to Remove these Ads

Please Register to Remove these Ads



Reply
  #1  
Old 01-09-2009, 05:50 PM
mewingkitty's Avatar
mewingkitty mewingkitty is offline
Valued Forum Contributor
 
Join Date: 29 Sep 2008
Location: Grande Prairie, Alberta, Canada
MS Office Version:Excel 2007
Posts: 902
mewingkitty has an addiction to Excel
Send a message via MSN to mewingkitty Send a message via Yahoo to mewingkitty
FOR, IF, NEXT... Error in VBA while attempting a simple code.

Please Register to Remove these Ads

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
This obviously isn't a functional code in its current state, but it does work of the - Next iFor - is something else, such as - cells(1, 2) = 3

Just wondering why it is everything else seems to work with IF this way, but NEXT keeps getting me errors.

Thanks!
__________________
=IF(AND(OR(BLONDE,BRUNETTE,REDHEAD),OR(MY PLACE,HER PLACE),ME),BOW-CHICKA-BOW-WOW,ANOTHER NIGHT ON THE INTERNET)

Last edited by mewingkitty; 01-09-2009 at 06:55 PM.
Reply With Quote
  #2  
Old 01-09-2009, 05:59 PM
Leith Ross's Avatar
Leith Ross Leith Ross is offline
Forum Moderator
 
Join Date: 15 Jan 2005
Location: San Francisco, Ca
MS Office Version:2000, 2003, & read 2007
Posts: 10,529
Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding
Send a message via AIM to Leith Ross
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
Reply With Quote
  #3  
Old 01-09-2009, 06:19 PM
mewingkitty's Avatar
mewingkitty mewingkitty is offline
Valued Forum Contributor
 
Join Date: 29 Sep 2008
Location: Grande Prairie, Alberta, Canada
MS Office Version:Excel 2007
Posts: 902
mewingkitty has an addiction to Excel
Send a message via MSN to mewingkitty Send a message via Yahoo to mewingkitty
Sorry about that.

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?
__________________
=IF(AND(OR(BLONDE,BRUNETTE,REDHEAD),OR(MY PLACE,HER PLACE),ME),BOW-CHICKA-BOW-WOW,ANOTHER NIGHT ON THE INTERNET)
Reply With Quote
  #4  
Old 01-09-2009, 06:38 PM
stanleydgromjr stanleydgromjr is offline
Valued Forum Contributor
 
Join Date: 10 Oct 2008
Location: Northeast Pennsylvania, USA
MS Office Version:Excel 2003, 2007.
Posts: 692
stanleydgromjr has an addiction to Excel
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

Have a great day,
Stan
__________________
Have a great day,
Stan

stanleydgromjr
Windows Vista Business, Excel 2003 and 2007
Reply With Quote
  #5  
Old 01-09-2009, 06:42 PM
mewingkitty's Avatar
mewingkitty mewingkitty is offline
Valued Forum Contributor
 
Join Date: 29 Sep 2008
Location: Grande Prairie, Alberta, Canada
MS Office Version:Excel 2007
Posts: 902
mewingkitty has an addiction to Excel
Send a message via MSN to mewingkitty Send a message via Yahoo to mewingkitty
Not exactly what I was hoping for.

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?
__________________
=IF(AND(OR(BLONDE,BRUNETTE,REDHEAD),OR(MY PLACE,HER PLACE),ME),BOW-CHICKA-BOW-WOW,ANOTHER NIGHT ON THE INTERNET)
Reply With Quote
  #6  
Old 01-09-2009, 06:45 PM
mudraker's Avatar
mudraker mudraker is offline
Forum Moderator
 
Join Date: 10 Nov 2003
Location: Melbourne, Australia
Posts: 4,235
mudraker has an addiction to Excel
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
__________________
Please Read Forum Rules Before Posting
Wrap VBA code by selecting the code and clicking the # icon or Read This
How To Cross Post politely

Top Excel links for beginners to Experts

If you are pleased with a member's answer then use the Scales icon to rate it
If my reply has assisted or failed to assist you I welcome your Feedback.
Reply With Quote
  #7  
Old 01-09-2009, 06:54 PM
mewingkitty's Avatar
mewingkitty mewingkitty is offline
Valued Forum Contributor
 
Join Date: 29 Sep 2008
Location: Grande Prairie, Alberta, Canada
MS Office Version:Excel 2007
Posts: 902
mewingkitty has an addiction to Excel
Send a message via MSN to mewingkitty Send a message via Yahoo to mewingkitty
That answers that

Alrighty then.
Thanks!
__________________
=IF(AND(OR(BLONDE,BRUNETTE,REDHEAD),OR(MY PLACE,HER PLACE),ME),BOW-CHICKA-BOW-WOW,ANOTHER NIGHT ON THE INTERNET)
Reply With Quote
  #8  
Old 01-09-2009, 07:21 PM
broro183 broro183 is offline
Forum Guru
 
Join Date: 03 Jan 2006
Location: London :-)
MS Office Version:2003 (work) & 2007 (home)
Posts: 1,825
broro183 is very confident of their ability broro183 is very confident of their ability broro183 is very confident of their ability broro183 is very confident of their ability
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
hth
Rob
__________________
Rob Brockett
Kiwi in the UK
Always learning & the best way to learn is to experience...
Reply With Quote
  #9  
Old 01-10-2009, 01:55 PM
mewingkitty's Avatar
mewingkitty mewingkitty is offline
Valued Forum Contributor
 
Join Date: 29 Sep 2008
Location: Grande Prairie, Alberta, Canada
MS Office Version:Excel 2007
Posts: 902
mewingkitty has an addiction to Excel
Send a message via MSN to mewingkitty Send a message via Yahoo to mewingkitty
Heeeeeyyyy

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
__________________
=IF(AND(OR(BLONDE,BRUNETTE,REDHEAD),OR(MY PLACE,HER PLACE),ME),BOW-CHICKA-BOW-WOW,ANOTHER NIGHT ON THE INTERNET)
Reply With Quote
  #10  
Old 01-10-2009, 06:45 PM
broro183 broro183 is offline
Forum Guru
 
Join Date: 03 Jan 2006
Location: London :-)
MS Office Version:2003 (work) & 2007 (home)
Posts: 1,825
broro183 is very confident of their ability broro183 is very confident of their ability broro183 is very confident of their ability broro183 is very confident of their ability
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
__________________
Rob Brockett
Kiwi in the UK
Always learning & the best way to learn is to experience...
Reply With Quote
  #11  
Old 01-10-2009, 08:21 PM
shg's Avatar
shg shg is offline
Forum Guru
 
Join Date: 20 Jun 2007
Location: The Great State of Texas
MS Office Version:2003, 2007
Posts: 18,493
shg makes giving solutions look like childsplay shg makes giving solutions look like childsplay shg makes giving solutions look like childsplay shg makes giving solutions look like childsplay shg makes giving solutions look like childsplay shg makes giving solutions look like childsplay shg makes giving solutions look like childsplay shg makes giving solutions look like childsplay shg makes giving solutions look like childsplay shg makes giving solutions look like childsplay
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
__________________
Entia non sunt multiplicanda sine necessitate.

Last edited by shg; 01-10-2009 at 09:32 PM.
Reply With Quote
  #12  
Old 01-10-2009, 08:50 PM
mudraker's Avatar
mudraker mudraker is offline
Forum Moderator
 
Join Date: 10 Nov 2003
Location: Melbourne, Australia
Posts: 4,235
mudraker has an addiction to Excel
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
__________________
Please Read Forum Rules Before Posting
Wrap VBA code by selecting the code and clicking the # icon or Read This
How To Cross Post politely

Top Excel links for beginners to Experts

If you are pleased with a member's answer then use the Scales icon to rate it
If my reply has assisted or failed to assist you I welcome your Feedback.
Reply With Quote
  #13  
Old 01-11-2009, 04:56 AM
broro183 broro183 is offline
Forum Guru
 
Join Date: 03 Jan 2006
Location: London :-)
MS Office Version:2003 (work) & 2007 (home)
Posts: 1,825
broro183 is very confident of their ability broro183 is very confident of their ability broro183 is very confident of their ability broro183 is very confident of their ability
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
__________________
Rob Brockett
Kiwi in the UK
Always learning & the best way to learn is to experience...
Reply With Quote
  #14  
Old 01-12-2009, 03:07 PM
mewingkitty's Avatar
mewingkitty mewingkitty is offline
Valued Forum Contributor
 
Join Date: 29 Sep 2008
Location: Grande Prairie, Alberta, Canada
MS Office Version:Excel 2007
Posts: 902
mewingkitty has an addiction to Excel
Send a message via MSN to mewingkitty Send a message via Yahoo to mewingkitty
Much appreciated

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!
__________________
=IF(AND(OR(BLONDE,BRUNETTE,REDHEAD),OR(MY PLACE,HER PLACE),ME),BOW-CHICKA-BOW-WOW,ANOTHER NIGHT ON THE INTERNET)
Reply With Quote


Reply

Bookmarks


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off

Forum Jump