killing a sub from within an if statement

[Guest]

I am trying to stop a sub from continueing once a set of values i.e. null in
2 cells is attained. Can anyone help me?

Range("A4:w4").Select
Do Until ActiveCell.Value = ""
'Moves #1 PM info down until date matches #4 PM date

If ActiveCell.Value < ActiveCell.Offset(0, 12).Value Then
ActiveCell.Offset(0, 12).Resize(, 11).Insert shift:=xlDown
MsgBox "Loop #1 Column A < Column M"

'If #4Pm's date is greater then #1 PM's date then shift #4 down.
ElseIf ActiveCell.Value > ActiveCell.Offset(0, 12).Value Then
ActiveCell.Resize(, 12).Insert shift:=xlDown
MsgBox "Loop #2 Column A > Column M
End If

If ActiveCell.Value = ActiveCell.Offset(0, 12).Value And
ActiveCell.Offset(1, 12).Value = "" Then
MsgBox "A = M AND M = Blank"
Kill statement needs to go here
End If

ActiveCell.Offset(1, 0).Select

Loop

End Sub

 

[Tom Ogilvy]

your test might be

if application.counta(activeCell.Value.Resize(2,1)) = 0 then

 

[Vasant Nanavati]

Exit Sub

 

[Guest]

Cliff

How about 'Exit Sub'?

Alok Joshi

 

[Guest]

Just ot be a little different. Why not create a flag variable and loop for so
long as the flag is true. Something like this...

Dim blnProcced as boolean

blnProceed = true
Range("A4:w4").Select
Do Until blnProceed = False
'Moves #1 PM info down until date matches #4 PM date

If ActiveCell.Value < ActiveCell.Offset(0, 12).Value Then
ActiveCell.Offset(0, 12).Resize(, 11).Insert shift:=xlDown
MsgBox "Loop #1 Column A < Column M"

'If #4Pm's date is greater then #1 PM's date then shift #4 down.
ElseIf ActiveCell.Value > ActiveCell.Offset(0, 12).Value Then
ActiveCell.Resize(, 12).Insert shift:=xlDown
MsgBox "Loop #2 Column A > Column M
End If

If ActiveCell.Value = ActiveCell.Offset(0, 12).Value And
ActiveCell.Offset(1, 12).Value = "" Then
MsgBox "A = M AND M = Blank"
Kill statement needs to go here
blnProceed = False
End If

ActiveCell.Offset(1, 0).Select

Loop

Doing it this way you can end the loop when every you want by changing the
boolean flag. I find Exit Sub to be problematic sometimes. There is a rule of
thumb that procedures and functions should have only one entry point and only
one exit point. This way you know that all of the code in the procedure was
addressed. Some of it may have been skipped, but all of it had the
opportunity to run...

HTH

HTH

 

[Guest]

Thanks folks. I knew it was simple, I just don't have the brains.

 

[Ed]

Be careful of objects and variables not properly terminated when you Exit
Sub. I would personally lean towards Jim's method of using a Boolean and
throw in a GoTo:
If Boolean = True Then GoTo StopMe
....
....
StopMe:
Terminate all variables
Terminate all objects
End Sub

No need then for Exit Sub, and everything's nice and neat (hopefully).

Ed

 

[Guest]

Why the Goto? The loop will exit when the flag is false and the sub you can
terminate any objects through normal execution. This is kind of the point to
the boolean. You don't just exit the sub. You complete whtever code is
necessary.

 

[Ed]

I think I see what're saying. I'm not real refined yet, and often do things
in a kludgy way. I see where you are denying the loop after it's run
through all code in the loop and the code would progress from there - and of
course, good programming would have all objects and variables tied up
anyway. My thought was that if there is code after where the kill
statement would go that the OP did not want to run, the GoTo would drop past
all that into the clean-up at the end. Maybe not the best way to accomplish
that, but like I said - not real refined yet.

Ed