How to change #DIV/0! result for an entire worksheet ?

[LG]

The worksheet I created has many forumlas where the 'divisor' can be zero.
The formulae are long and complicated with nested functions. So, I don't
want to add the If statement for a zero divisor to each cell that can result
in a DIV/0.
Is there a way that I can for an entire spread sheet at once modify the
result from DIV/0 to blank?

 

[DarkByte]

Use number formatting

like : 0.00,,,""
or General,,,""

it goes like: <positive value>,<negative value>,<zero value>,<error value>

 

[Dave Peterson]

Maybe you can use format|conditional format to hide the error (white font on
white fill)???

=iserror(a1)
would be how I'd use that conditional format formula (for A1)

 

[Dave Peterson]

Did that really work for you?

Use number formatting

like : 0.00,,,""
or General,,,""

it goes like: <positive value>,<negative value>,<zero value>,<error value>

 

[Gord Dibben]

LG

Can you use a macro?

Select all cells in question and run the macro.

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