Gord,

A good point, I never thought of that but for the formula I still prefer

F = Mid(c.Formula, 2)

instead of

myStr = Right(cel.Formula, Len(cel.Formula) - 1)

Mike

"Gord Dibben" wrote:

Just for info.

Mike's macro does not check to see if ISERROR already exists in the
formula(s)

It won't matter to the result but it does make formulas a lot longer and
look more complex if ISERROR gets doubled up.

=IF(ISERROR(IF(ISERROR(A2&A3),"",A2&A3)),"",IF(ISE RROR(A2&A3),"",A2&A3))

I prefer to check as I go through the selected range.

Sub ErrorTrapAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISERROR*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISERROR(" & myStr & "),""""," & myStr & ")"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP


On Wed, 23 Dec 2009 09:50:01 -0800, ORLANDO VAZQUEZ <Orlando Vazquez wrote:

This worked perfectly.

Thank you so much Mike.


"Mike H" wrote:

Hi,

Try this macro. Select the formula you want to change and run it. Try it in
a test environment first

Sub Sonic()
For Each c In Selection
If c.HasFormula Then
F = Mid(c.Formula, 2)
c.Formula = "=IF(ISERROR(" & F & "),""""," & F & ")"
End If
Next
End Sub

Mike

"ORLANDO VAZQUEZ" wrote:

Hi,

I have several hundred different formulas on one spreadsheet like this one.

=VLOOKUP($C$1,'P:\Rate Sheets\client name\[file name.xls]manager
name'!$BV$67:$CL$131,3,FALSE)

The results sometimes return #DIV/0!, or other error messages which ruin my
attempts at summing the results as well as the visual appeal.

Is there a way to rewrite (en masse) these formulas to so that they first
check "If(iserror(myformula),[then] 0, [else] myformula) so that rather then
an error result I will see a zero (0).?

I can manually change them but that would take a day at least.

Please let me know.

Thank you,
Orlando

.