Correcting Multiple Div#0s

  • Thread starter Thread starter ANTBOH
  • Start date Start date
A

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
 
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
 
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
 
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 "=".
 
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.
 
Back
Top