Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Green Bay, Wisconsin, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Eliminate Error Messages (Excel 2003)

    I have a worksheet that calculates values in a table based upon a selected data set from another sheet. In some cases, values are missing from the selected data set. The missing data causes #DIV/0, #N/A, or other error messages to be inserted in the table. I want to eliminate these messages and just leave the cell blank when data is missing.

    In the past, I have worked around this problem by entering an IF function in each cell of the table similar to this:
    If(iserror(formula),"",formula)
    This eliminates the error messages, but it is quite cumbersome, especially when the formula is long. Debugging and formula changes are difficult because the formula must be checked and/or changed twice in each cell of the table. Is there an easier way to eliminate the error messages?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Eliminate Error Messages (Excel 2003)

    The only alternative would be to build specific tests into the formulas, e.g. in a division:
    <pre>=IF(A1=0,"",B1/A1)
    </pre>

    instead of
    <pre>=IF(ISERROR(B1/A1),"",B1/A1)
    </pre>

    but it's still cumbersome.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Eliminate Error Messages (Excel 2003)

    See if this VBA subroutine will help.
    AddISERROR will take a formula and wrap "=IF(ISERROR(FORMULA),"",FORMULA)" if there is no ISERROR already in the formula. If you have a really long formula this might create a resulting formula too long for XL to handle.

    <pre>
    Sub AddISERROR()
    Dim rngCell As Range
    Dim I As Long
    Dim iCalculationState As Integer
    Dim Work As String
    iCalculationState = Application.Calculation
    Application.Calculation = xlCalculationManual
    For Each rngCell In Selection
    If rngCell.HasFormula Then
    If InStr(rngCell.Formula, "ISERROR") = 0 Then
    Work = Right(rngCell.Formula, Len(rngCell.Formula) - 1)
    rngCell.Formula = "=IF(ISERROR(" & Work _
    & "),""""," & Work & ")"
    End If
    End If
    Next
    Application.Calculation = iCalculationState
    Application.Calculate
    End Sub

    </pre>



    Also, I recall some months back a user-defined error function created by some loungers
    that only required one instance of the formula being tested, not two.
    Later Found it!: <post#=87,625>post 87,625</post: >
    Ken

Posting Permissions

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