| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
killing a sub from within an if statement
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
killing a sub from within an if statement
your test might be
if application.counta(activeCell.Value.Resize(2,1)) = 0 then -- Regards, Tom Ogilvy "Cliff L" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
killing a sub from within an if statement
Exit Sub
-- Vasant "Cliff L" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
killing a sub from within an if statement
Cliff
How about 'Exit Sub'? Alok Joshi "Cliff L" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
killing a sub from within an if statement
Thanks folks. I knew it was simple, I just don't have the brains.
"Alok" wrote: Cliff How about 'Exit Sub'? Alok Joshi "Cliff L" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
killing a sub from within an if statement
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 "Cliff L" wrote in message ... Thanks folks. I knew it was simple, I just don't have the brains. "Alok" wrote: Cliff How about 'Exit Sub'? Alok Joshi "Cliff L" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
killing a sub from within an if statement
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" wrote: 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 "Cliff L" wrote in message ... Thanks folks. I knew it was simple, I just don't have the brains. "Alok" wrote: Cliff How about 'Exit Sub'? Alok Joshi "Cliff L" wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
killing a sub from within an if statement
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 "Jim Thomlinson" wrote in message ... 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" wrote: 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 "Cliff L" wrote in message ... Thanks folks. I knew it was simple, I just don't have the brains. "Alok" wrote: Cliff How about 'Exit Sub'? Alok Joshi "Cliff L" wrote: 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
killing a sub from within an if statement
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 "Cliff L" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Expiring/Killing Macro(s) | Excel Discussion (Misc queries) | |||
| Data collation killing me!! | Excel Worksheet Functions | |||
| VBA loop is killing me! | Excel Discussion (Misc queries) | |||
| Killing the split window | Setting up and Configuration of Excel | |||
| Killing Excel :) | Excel Programming | |||
Hybrid Mode
