macro - replace

[Rohit]

hi, i have various worksheets and within that worksheet there are cells
having #DIV/0!. I want this to be replace by zero. i know there is a
formula which will give out zero but if someone can write a macro would good.

Thanks

 

[Gord Dibben]

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

 

[FSt1]

hi
the formula is the microsoft way of dealing with the problem.
see this site.
http://support.microsoft.com/kb/182188
i know of no macro command that would make the #DEV/0! error go away other
than to replace the formula with zero or replace the formula with the above
formula.

regards
FSt1