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

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

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

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