Correcting Multiple Div#0s

[ANTBOH]

Hi guys

I have a big column of formulae that contains lots of div#0s. All formula
are different. Is there anyway I can remove the div#0s (to simply leave a 0
or a blank) without having to rewrite each formula?

Thanks for the help!

ANT

 

[Eva]

Put in front of your formula:
If(iserror(copy your formula)=true,0,your formula again)
or on the next column
IF(ISERROR(C1)=TRUE,0,C1)

Click yes if helped

 

[ANTBOH]

But I would have to go through and do that singularly for each formula?

 

[Eduardo]

Hi,
unfortunately you can't you need to rewrite your formula a suggested by Eva

 

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

NOTE: the use of ISERROR will mask all errors, not just #DIV/0!


Gord Dibben MS Excel MVP

 

[Luke M]

Might be able to do this with multiple steps. First, select cells, and do a
find and replace to remove the leading "=" sign. Create a helper column, and
do something like this:
="zzz=IF(ISERROR("&A2&"),0,"&A2&")
Copy the helper column, paste special as values.
Do a find & replace on this column changing "zzz=" to just "=".

 

[Eva]

You need to change your formula once, and then copy the new formula down.
So you don't need to make changes on each formula.