Replace formula with IF(error(formula;"";formula)
 

Hi all

Do you think is there any macro that can change the formula in selected
range to include the IF-iserror so no error appears on the screen?

For example:
In cell A1 the formula is: =A2+B2
But if I select A1 and run the macro, It would change to:
=IF(ISERROR(A2+B2);"";(A2+B2))

I need that beacause I have a sheet with lots of formulas with different
errors and It would be quite painful to chang every formula.

Thanks you for your help!!!!

Replace formula with IF(error(formula;"";formula)
 

I have found the answer:

Sub ErrorTrapAddDDL()

' Adds =If(IsError() around formulas

Dim cel As Range
Dim rng As Range
Dim Check As String

Const Equ As String = "=IF(ISERROR(_x) ,"""", _x)"

Check = Left$(Equ, 12) & "*" ' Check for =IF(ISERROR(

On Error Resume Next

Set rng = Selection.SpecialCells(xlFormulas, 23)
If rng Is Nothing Then Exit Sub

With WorksheetFunction
For Each cel In rng
If Not cel.Formula Like Check Then
cel.Formula = .Substitute(Equ, "_x", Mid$(cel.Formula, 2))
End If
Next
End With
End Sub


Thanks anyway!

"lecaballero" escribió:


Hi all

Do you think is there any macro that can change the formula in selected
range to include the IF-iserror so no error appears on the screen?

For example:
In cell A1 the formula is: =A2+B2
But if I select A1 and run the macro, It would change to:
=IF(ISERROR(A2+B2);"";(A2+B2))

I need that beacause I have a sheet with lots of formulas with different
errors and It would be quite painful to chang every formula.

Thanks you for your help!!!!