Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Bournemouth, Dorset, England
    Thanked 0 Times in 0 Posts

    ISERROR Function (xl97)

    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).

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: ISERROR Function (xl97)

    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 & ")"
    End Sub</pre>

    It will replace all formulas that currently in an error from something like:

    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.


  3. #3
    Join Date
    Jan 2004
    Derry, Derry, Ireland, Northern
    Thanked 0 Times in 0 Posts

    Re: ISERROR Function (xl97)


    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts