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
--
Greatly appreciated
Eva


"ANTBOH" wrote:

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

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

"Eva" wrote:

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
--
Greatly appreciated
Eva


"ANTBOH" wrote:

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

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

"ANTBOH" wrote:

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

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

On Tue, 19 Jan 2010 08:13:02 -0800, ANTBOH
wrote:

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

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 "=".
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ANTBOH" wrote:

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

"Eva" wrote:

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
--
Greatly appreciated
Eva


"ANTBOH" wrote:

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

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.
--
Greatly appreciated
Eva


"ANTBOH" wrote:

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

"Eva" wrote:

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
--
Greatly appreciated
Eva


"ANTBOH" wrote:

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