Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Green Bay, Wisconsin, USA
    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:
    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
    Thanked 28 Times in 28 Posts

    Re: Eliminate Error Messages (Excel 2003)

    The only alternative would be to build specific tests into the formulas, e.g. in a division:

    instead of

    but it's still cumbersome.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Sault Ste. Marie, Michigan, USA
    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.

    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
    Application.Calculation = iCalculationState
    End Sub


    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: >

Posting Permissions

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