One method.

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 & "),0," & mystr & ")"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Wed, 31 Mar 2010 11:00:01 -0700, Jamie
wrote:

Hi,

I have a worksheet that has hundreds of formulas and many of them are
generating a #DIV/0! error. What I would like to do is creat an IF function
in order to show the #DIV/0! as 0%.

For example:

If I have a formula in C1 that looks like this =A1/B1 and B1 is zero I will
get the #DIV/0!. I want to change the formula to look like this
=if(B1=0,0,A1/B1).

Now picture a worksheet that has hundreds of these errors. Manually going in
and changing the formula would take hours.

Does anyone know of a macro that will automatically take the number I am
dividing by and create a IF function? If you know of a better way to get
around the issue other than using the IF function I am open to suggestions.

Also, If you do know of a macro it would be very helpful if would work on
any formula that has one number being divided.
Example: The macro will work whether the original formula is as simply as
=A1/B1 or if it is more complex such as =((A1+B1+C1)*D1)/B1.

Thanks