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


--
cparaske
------------------------------------------------------------------------
cparaske's Profile: http://www.excelforum.com/member.php...fo&userid=3793
View this thread: http://www.excelforum.com/showthread...hreadid=378646

Hi

Code:
--------------------

Sub XXX()
Dim r As Range, myStr As String
myStr = "=" & Chr(34) & "XXX" & Chr(34)
Set r = Cells.Find(What:=myStr, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not r Is Nothing Then
r = r.Value
While Not r Is Nothing
Set r = Cells.FindNext(r)
If Not r Is Nothing Then
r = r.Value
End If
Wend
End If
End Sub

--------------------

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


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=378646

Thanks, jindon.

It worked like a charm.

Kind regards,
Cristo


--
cparaske
------------------------------------------------------------------------
cparaske's Profile: http://www.excelforum.com/member.php...fo&userid=3793
View this thread: http://www.excelforum.com/showthread...hreadid=378646