Macro to get rid of #DIV/0!

[Jamie]

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

 

[Gord Dibben]

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

 

[Bernard Liengme]

I would be very dubious of a macro that purported to do what you are asking.
Hoe will it identify the divisor? Your example all had it as the last two
symbols as in =((A1+B1+C1)*D1)/B1 but what about =((A1+B1+C1)/D1)*B1

I suppose you might be able to use logic in the form
If oCell.hasFormula then
oCell.Formula = "if(iserror(oCell.Formula),0,oCell.formula)"
end if

Is your concern just the look of the worksheet? If so, why not use
conditional formatting such as Formula Is =ISERROR(A1) and format the font
colour to match the cell background herby making the DIV error invisible on
screen and in print?

best wishes