Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop the macro at the end of a certain column
Can anyone tell me how to write the string of code to stop a macro at a
certain point? I have the following macro that runs through a range but because I need to use the cell.find and activate mode when it loops around it ignores the range and continues on, I am thinking if I could just stop it at the bottom of column AU where I have a "999" then it would solve my problems. Sub FindOnes() Dim r As Range Dim Topcell As Variant Dim Bottomcell As Variant Set r = ActiveSheet.Range("E3:AU65535") For Each c In r Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase: = _ False, SearchFormat:=True).Activate If ActiveCell.Value = 1 Then Set Topcell = ActiveCell Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase: = _ False, SearchFormat:=True).Activate If ActiveCell.Value = 2 Then Set Bottomcell = ActiveCell If Topcell = 1 Then Set Topcell = Topcell Range(Topcell, Bottomcell).Select Selection.FillDown Bottomcell.Select End If End If ???????Stop at the end of column AU??????? Next End Sub -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop the macro at the end of a certain column
???????Stop at the end of column AU???????
Put this in where the above line appears: If c.Address = "AU65535" Then Exit For End If "Carrie_Loos via OfficeKB.com" wrote: Can anyone tell me how to write the string of code to stop a macro at a certain point? I have the following macro that runs through a range but because I need to use the cell.find and activate mode when it loops around it ignores the range and continues on, I am thinking if I could just stop it at the bottom of column AU where I have a "999" then it would solve my problems. Sub FindOnes() Dim r As Range Dim Topcell As Variant Dim Bottomcell As Variant Set r = ActiveSheet.Range("E3:AU65535") For Each c In r Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase: = _ False, SearchFormat:=True).Activate If ActiveCell.Value = 1 Then Set Topcell = ActiveCell Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase: = _ False, SearchFormat:=True).Activate If ActiveCell.Value = 2 Then Set Bottomcell = ActiveCell If Topcell = 1 Then Set Topcell = Topcell Range(Topcell, Bottomcell).Select Selection.FillDown Bottomcell.Select End If End If ???????Stop at the end of column AU??????? Next End Sub -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop the macro at the end of a certain column
You can also use the word "Stop".
Stop does the same thing that a breakpoint does when you are editing code. It temporarily halts the macro. You can restart the macro by clicking the continue arrow in the VBE. "Carrie_Loos via OfficeKB.com" wrote: Can anyone tell me how to write the string of code to stop a macro at a certain point? I have the following macro that runs through a range but because I need to use the cell.find and activate mode when it loops around it ignores the range and continues on, I am thinking if I could just stop it at the bottom of column AU where I have a "999" then it would solve my problems. Sub FindOnes() Dim r As Range Dim Topcell As Variant Dim Bottomcell As Variant Set r = ActiveSheet.Range("E3:AU65535") For Each c In r Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase: = _ False, SearchFormat:=True).Activate If ActiveCell.Value = 1 Then Set Topcell = ActiveCell Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase: = _ False, SearchFormat:=True).Activate If ActiveCell.Value = 2 Then Set Bottomcell = ActiveCell If Topcell = 1 Then Set Topcell = Topcell Range(Topcell, Bottomcell).Select Selection.FillDown Bottomcell.Select End If End If ???????Stop at the end of column AU??????? Next End Sub -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop the macro at the end of a certain column
Thanks - still not quite there yet.
What I ended up doing is putting the statement after the "find" portion for a 1 and placing a 1 in cell AU65535. When I step through the macro it does run correctly and end up on cell AU65535 but it does not recoginze the c.Address and skips to the End if. In leiu of c.Address I have also tried ActiveCell and/or range TopCell with no luck, I even changed the orginal set range to end at AU65534. Is there any other way to identify the cell so that the macro exits or stops? JLGWhiz wrote: ???????Stop at the end of column AU??????? Put this in where the above line appears: If c.Address = "AU65535" Then Exit For End If Can anyone tell me how to write the string of code to stop a macro at a certain point? I have the following macro that runs through a range but [quoted text clipped - 40 lines] End Sub -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I stop scrolling from column to column ? | Excel Worksheet Functions | |||
Column stop | Excel Programming | |||
how do I set my tab to stop at column N and rtn to A | Excel Discussion (Misc queries) | |||
Macro: With Stop it works. Without Stop it doesn't. | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |