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

[kbishop94]

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

 

[kbishop94]

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.

 

[mumps]

Try:

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

 

[Fluff]

If you want to exit the loop, replace Exit Sub with Exit For

 

[kbishop94]

Try:

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.

 

[kbishop94]

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

thanks (once again!) Fluff.

 

[Fluff]

You're welcome & thanks for the feedback