Ending Looping Macro

[cparaske]

I've got this code that is giving me an error (Run time Error '91').

The code is looking for formulas that begin with "=XXX" and copy/paste values any cell that contains this formula.

Here is the code:
Sub XXX()
Cells.Find(What:="=XXX", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Sub XXX_Loop()
Do
XXX
Loop
End Sub

You can probably tell that when Excel doesn't find any additional "=XXX" formulas, the error message pops up. I've tried a few things and nothing seems to work.

Although the macro is doing what it needs to do, I'd like to have a "cleaner" running macro that doesn't end with an error message. Any help will be greatly appreciated.

EDIT: The simple solution would be to copy/paste values the whole worksheet but I would like to leave some "live" formulas (e.g. "=A24/A25") that do not start with the "=XXX" custom function.

Thanks,
cparaske

[jindon]

Hi

Please Login or Register  to view this content.

I've got this code that is giving me an error (Run time Error '91').

The code is looking for formulas that begin with "=XXX" and copy/paste values any cell that contains this formula.

Here is the code:
Sub XXX()
Cells.Find(What:="=XXX", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Sub XXX_Loop()
Do
XXX
Loop
End Sub

You can probably tell that when Excel doesn't find any additional "=XXX" formulas, the error message pops up. I've tried a few things and nothing seems to work.

Although the macro is doing what it needs to do, I'd like to have a "cleaner" running macro that doesn't end with an error message. Any help will be greatly appreciated.

Thanks,
cparaske

[cparaske]

Thanks, jindon.

It worked like a charm.

Kind regards,
Cristo