ISERROR Function (xl97)

[st_321]

Does anyone have any code that can help input the ISERROR function into various cells with different formulas, that would leave the cell blank (i.e. "") if an error is found.

Obviously I can work through the spreadsheet and change cells individually, but it would be far easier and quicker if I could run a quick macro to look at the cell formula, and add "IF(ISERROR(" to the front of it (with the "" in the middle and the corresponding ending representing a copy of the first part of the formula).

[sdckapr]

Is this what you are after?

<pre>Option Explicit
Sub MaskErrors()
Dim rCell As Range
Dim rng As Range
Dim sNoEqual As String

Set rng = Nothing
On Error Resume Next
Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las, xlErrors)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "No formulas result in errors"
Exit Sub
End If

For Each rCell In rng
sNoEqual = Mid(rCell.Formula, 2)
rCell.Formula = "=if(iserror(" & sNoEqual & "), " _
& Chr(34) & Chr(34) & "," & sNoEqual & ")"
Next
End Sub</pre>


It will replace all formulas that currently in an error from something like:
<pre>=Formula(A,B,C)</pre>


to something like:
<pre>=if(iserror(Formula(A,B,C)), "", Formula(A,B,C))</pre>


You might have to run it again as the numbers change to get formulas with values now that get results later.

This can make it sluggish since it doesn't find the root of the problem, it just masks it. The problem could be an incorrect formula or a mistyped rangename, which will be harder to find and correct without the "error" listed.

Steve

[Meloncolly]

Hi,

The code I have posted below is very similar to Steve's, expect it applies the iserror check to all cells with formulas (formulae?), whether or not there is currently an error in the cell.

As Steve points out, it's not solving the root problem, just hiding it.

Sub amend_formula()
Dim FormulaCells
Dim CurrentFormula
Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)
' Exit if no formulas are found
If FormulaCells Is Nothing Then
MsgBox "No formulas found. "
Exit Sub
End If
' Process each formula
For Each Cell In FormulaCells
'assign cell formula to variable
CurrentFormula = Cell.Formula
'remove the equals sign from the current formula
CurrentFormula = Right(CurrentFormula, (Len(CurrentFormula) - 1))
'append the iserror and if functions to the start and end
Cell.Formula = "=if(iserror(" & CurrentFormula & ")," & """" & """" & "," & CurrentFormula & ")"
Next Cell
End Sub