Correcting Multiple Div#0s
 

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

Correcting Multiple Div#0s
 

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

Correcting Multiple Div#0s
 

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

Correcting Multiple Div#0s
 

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

Correcting Multiple Div#0s
 

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

Correcting Multiple Div#0s
 

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

Correcting Multiple Div#0s
 

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