Automatically Changing Functions

[bmstar]

I have over 720 functions across four worksheets that commonly results in #Div/0 errors. I want to insert an "error handling" function to make those #Div/0 errors disappear. That's the easy part. I am using:

=IF(ISERROR(OriginalFormula),"",OriginalFormula)

--> "Original Function" could be as simple as: =((A1+A5)/(B1-B8))

My problem is that I don't want to manually go to each cell and cut, paste, cut, paste, etc. to make the alterations to each formula.

Is there any macro that will make each "OriginalFormula" a variable, and automatically paste the variable (original function) back into my desired new function above?

Thanks....

[Ken Johnson]

bmstar wrote:
> I have over 720 functions across four worksheets that commonly results
> in #Div/0 errors. I want to insert an "error handling" function to
> make those #Div/0 errors disappear. That's the easy part. I am
> using:
>
> =IF(ISERROR(OriginalFormula),"",OriginalFormula)
>
> --> "Original Function" could be as simple as: =((A1+A5)/(B1-B8))
>
> My problem is that I don't want to manually go to each cell and cut,
> paste, cut, paste, etc. to make the alterations to each formula.
>
> Is there any macro that will make each "OriginalFormula" a variable,
> and automatically paste the variable (original function) back into my
> desired new function above?
>
> Thanks....


Hi bmstar,

this worked for me...

Public Sub ChangeToHandleError()
Dim rngCell As Range
Dim strFormula As String
For Each rngCell In Application.Selection
If Left(rngCell.Formula, 1) = "=" Then
strFormula = Right(rngCell.Formula, Len(rngCell.Formula) - 1)
rngCell.Formula = "=IF(ISERROR(" & strFormula & _
"),""""," & strFormula & ")"
End If
Next
End Sub

It works on the range of cells that you select before running.

Try it out on a copy of your sheet first.

Ken Johnson

[Ken Johnson]

Hi bmstar,

just a minor improvement so that the code doesn't change any formulas
in the selected range that are already set up to hide the DIV0 error,
which results in unnecessarily long formulas...

Public Sub ChangeToHandleError()
Dim rngCell As Range
Dim strFormula As String
For Each rngCell In Application.Selection
If Left(rngCell.Formula, 1) = "=" _
And Left(rngCell.Formula, 11) <> "=IF(ISERROR" Then
strFormula = Right(rngCell.Formula, Len(rngCell.Formula) - 1)
rngCell.Formula = "=IF(ISERROR(" & strFormula & _
"),""""," & strFormula & ")"
End If
Next
End Sub

Ken Johnson