Macro to get rid of #DIV/0!
in [Functions]
|
Prev: Relative Range Reference in a sumifs formula
Next: multiple cells are often selected when I click in only one cell
From: Jamie on 31 Mar 2010 14:00 Hi, I have a worksheet that has hundreds of formulas and many of them are generating a #DIV/0! error. What I would like to do is creat an IF function in order to show the #DIV/0! as 0%. For example: If I have a formula in C1 that looks like this =A1/B1 and B1 is zero I will get the #DIV/0!. I want to change the formula to look like this =if(B1=0,0,A1/B1). Now picture a worksheet that has hundreds of these errors. Manually going in and changing the formula would take hours. Does anyone know of a macro that will automatically take the number I am dividing by and create a IF function? If you know of a better way to get around the issue other than using the IF function I am open to suggestions. Also, If you do know of a macro it would be very helpful if would work on any formula that has one number being divided. Example: The macro will work whether the original formula is as simply as =A1/B1 or if it is more complex such as =((A1+B1+C1)*D1)/B1. Thanks
From: Gord Dibben on 31 Mar 2010 14:12 One method. 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 & "),0," & mystr & ")" End If End If Next End Sub Gord Dibben MS Excel MVP On Wed, 31 Mar 2010 11:00:01 -0700, Jamie <Jamie(a)discussions.microsoft.com> wrote: >Hi, > >I have a worksheet that has hundreds of formulas and many of them are >generating a #DIV/0! error. What I would like to do is creat an IF function >in order to show the #DIV/0! as 0%. > >For example: > >If I have a formula in C1 that looks like this =A1/B1 and B1 is zero I will >get the #DIV/0!. I want to change the formula to look like this >=if(B1=0,0,A1/B1). > >Now picture a worksheet that has hundreds of these errors. Manually going in >and changing the formula would take hours. > >Does anyone know of a macro that will automatically take the number I am >dividing by and create a IF function? If you know of a better way to get >around the issue other than using the IF function I am open to suggestions. > >Also, If you do know of a macro it would be very helpful if would work on >any formula that has one number being divided. >Example: The macro will work whether the original formula is as simply as >=A1/B1 or if it is more complex such as =((A1+B1+C1)*D1)/B1. > >Thanks
From: Bernard Liengme on 31 Mar 2010 14:33
I would be very dubious of a macro that purported to do what you are asking. Hoe will it identify the divisor? Your example all had it as the last two symbols as in =((A1+B1+C1)*D1)/B1 but what about =((A1+B1+C1)/D1)*B1 I suppose you might be able to use logic in the form If oCell.hasFormula then oCell.Formula = "if(iserror(oCell.Formula),0,oCell.formula)" end if Is your concern just the look of the worksheet? If so, why not use conditional formatting such as Formula Is =ISERROR(A1) and format the font colour to match the cell background herby making the DIV error invisible on screen and in print? best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Jamie" <Jamie(a)discussions.microsoft.com> wrote in message news:E8578AA0-85E8-4FA8-9D9B-9E0BE6E7B046(a)microsoft.com... > Hi, > > I have a worksheet that has hundreds of formulas and many of them are > generating a #DIV/0! error. What I would like to do is creat an IF > function > in order to show the #DIV/0! as 0%. > > For example: > > If I have a formula in C1 that looks like this =A1/B1 and B1 is zero I > will > get the #DIV/0!. I want to change the formula to look like this > =if(B1=0,0,A1/B1). > > Now picture a worksheet that has hundreds of these errors. Manually going > in > and changing the formula would take hours. > > Does anyone know of a macro that will automatically take the number I am > dividing by and create a IF function? If you know of a better way to get > around the issue other than using the IF function I am open to > suggestions. > > Also, If you do know of a macro it would be very helpful if would work on > any formula that has one number being divided. > Example: The macro will work whether the original formula is as simply as > =A1/B1 or if it is more complex such as =((A1+B1+C1)*D1)/B1. > > Thanks |