How to stop a loop in Excel Macro 1

[thestampdad]

Below is part of a macro I have in Excel which is stuck in a loop.

How do I stop the loop once the last cell has been found and dealt with? I would like the macro to automatically continue without any manual intervention.


keep_going:

Cells.Find(What:=" Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.End(xlToRight).Select
Selection.Font.Bold = True
ActiveCell.Offset(-1, -3).Activate
Selection.Font.Bold = True
ActiveCell.Offset(1, 3).Activate

GoTo keep_going

Thank you for any help you can provide.

Dave.

http://www.thestampdad.com

 

[PHV]

Something like this ?
Dim f
keep_going:
Set f = Cells.Find(What:=" Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not f Is Nothing Then
f.Activate
Selection.End(xlToRight).Select
Selection.Font.Bold = True
ActiveCell.Offset(-1, -3).Activate
Selection.Font.Bold = True
ActiveCell.Offset(1, 3).Activate
GoTo keep_going
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[longhair]

thestamppad,
imho 'GoTo' is bad. this macro creates the infinate loop. what i would do is define the range that you need to search and use a for loop.
regards,
longhair

 

[Zathras]

Not only is the goto unnecessary, it is seldom necessary to select the cells you are manipulating. (And usually slower.)

Here is a routine that is functionally equivalent to the posted code for you to experiment with:

Sub Test()
Dim c As Range
Dim nLastRowFound As Long

Set c = Cells.Find(What:=" Total", After:=Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not c Is Nothing Then
  nLastRowFound = 0
  While c.Row > nLastRowFound
    nLastRowFound = c.Row
    c.End(xlToRight).Font.Bold = True
    c.End(xlToRight).Offset(-1, -3).Font.Bold = True
    Set c = Cells.FindNext(c)
  Wend
End If

 

[thestampdad]

Thanks PH.

I pasted your code into my macro but it still loops. Perhaps I am missing something. I ran your code as a separate macro.


I am not a macro expert, I do these by usually recording them not actually writing them. Some code I have "borrowed" from other sources.

Thanks,

The Rookie.

 

[thestampdad]

Thank you very much Zathras. This worked perfectly.

I don't understand it but it works. I will need to spend some time learning more about coding macros.

Dave.