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>

"LG" wrote:

> 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?
>
>

[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)

LG wrote:
>
> 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?

--

Dave Peterson

[Dave Peterson]

Did that really work for you?

DarkByte wrote:
>
> Use number formatting
>
> like : 0.00,,,""
> or General,,,""
>
> it goes like: <positive value>,<negative value>,<zero value>,<error value>
>
> "LG" wrote:
>
> > 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?
> >
> >

--

Dave Peterson

[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

On Fri, 26 May 2006 07:52:02 -0700, LG <[email protected]> wrote:

>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?
>