problem with exiting loop after 'If Then Else' procedure is executed...

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
457
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Code:
Dim iP As Variant
Dim cntRows As Long
cntRows = ActiveWorkbook.Worksheets("ANALYTICS").Range("F65536").End(xlUp).Row

For Each iP In Range(Cells(1, 6), Cells(cntRows, 16))
    If cntRows <= 15 Then
    MsgBox "there are less than 15 rows in your tally"
[COLOR=#008000][B]    'Exit Sub[/B][/COLOR]
    End If
    If cntRows > 15 And cntRows <= 30 Then
    MsgBox "you have more than 15 rows, but less than 31 rows in your tally"
[B][COLOR=#008000]    'Exit Sub[/COLOR][/B]
    End If
    If cntRows > 30 And cntRows <= 45 Then
    MsgBox "you have more than 30 rows, but less than 46 rows in your tally"
[B][COLOR=#008000]    'Exit Sub[/COLOR][/B]
    End If
    If cntRows > 45 And cntRows <= 60 Then
    MsgBox "you have more than 45 rows, but less than 61 rows in your tally"
[B][COLOR=#008000]    'Exit Sub[/COLOR][/B]
    End If
    If cntRows > 60 Then
    MsgBox "you have more than 60 rows in your tally"
[B][COLOR=#008000]    'Exit Sub[/COLOR][/B]
    End If
Next

So my problem is that if I uncomment each of the "Exit Sub"'s in the code above then it exits the loop just fine, but it also exits the entire module (this code is executed as part of the code from a cmd-button execution on a userform.)
What I really need it to do is just leave this individual procedure (ignoring the other "If" 's in the procedure that do not meet the criteria) and then continue on with the remaining code in this module (which is directing over to a worksheet which shows the results of what is chosen/selected on the userform.)

The way it sits right now (with the Exit Sub's commented out) it gets in a loop for the particular MsgBox that meets the criteria and it then just repeats it about a gazzilion times (ok, reallymore like maybe a 100 or so...) But, when its done with its annoying loop, it does correctly move on to the remainder of the module/procedure and it finishes that out just fine and as expected.
What do I need to change in the code so that after the correct 'If' is determined and executed it ignores the other 'Ifs' and then moves on to the rest of the code and doest just exit the sub? Thanks!

PS- FWIW... I am just using the message boxes in the above code as temporary placeholders just untill I get the code working the way I need it to... When I get this 'If-then-else-loop' thing sorted out I can then put the correct code in place of the message boxes (which will be to format and copy a range of rows from one worksheet to another worksheet.)
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
PSS- I tried multiple different ways of using ElseIf but I could't get it working at all no matter how I had the code written.
 
Upvote 0
Try:
Code:
For Each iP In Range(Cells(1, 6), Cells(cntRows, 16))
    If cntRows <= 15 Then
        MsgBox "there are less than 15 rows in your tally"
    ElseIf cntRows > 15 And cntRows <= 30 Then
        MsgBox "you have more than 15 rows, but less than 31 rows in your tally"
    ElseIf cntRows > 30 And cntRows <= 45 Then
        MsgBox "you have more than 30 rows, but less than 46 rows in your tally"
    ElseIf cntRows > 45 And cntRows <= 60 Then
        MsgBox "you have more than 45 rows, but less than 61 rows in your tally"
    ElseIf cntRows > 60 Then
        MsgBox "you have more than 60 rows in your tally"
    End If
Next
 
Upvote 0
If you want to exit the loop, replace Exit Sub with Exit For
 
Upvote 0
Try:
Code:
For Each iP In Range(Cells(1, 6), Cells(cntRows, 16))
    If cntRows <= 15 Then
        MsgBox "there are less than 15 rows in your tally"
    ElseIf cntRows > 15 And cntRows <= 30 Then
        MsgBox "you have more than 15 rows, but less than 31 rows in your tally"
    ElseIf cntRows > 30 And cntRows <= 45 Then
        MsgBox "you have more than 30 rows, but less than 46 rows in your tally"
    ElseIf cntRows > 45 And cntRows <= 60 Then
        MsgBox "you have more than 45 rows, but less than 61 rows in your tally"
    ElseIf cntRows > 60 Then
        MsgBox "you have more than 60 rows in your tally"
    End If
Next

Thanks, but this still keeps it in that annoying loop. :confused:
 
Upvote 0
If you want to exit the loop, replace Exit Sub with Exit For


I swear I had tried just this when I was trying to get it to work before I gave up and posted for help! lol When i saw what you posted, I said to myself "nope, already tried that! its not going to work."

But I tried it anyway.

And of course it worked.

And then I was like :oops:

thanks (once again!) Fluff. (y)
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top