Eliminate Error Messages (Excel 2003)

Topic

New Reply

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?

Reply | Quote

Replies

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

=IF(A1=0,"",B1/A1)

instead of

=IF(ISERROR(B1/A1),"",B1/A1)

but it’s still cumbersome.

Reply | Quote

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
Next
Application.Calculation = iCalculationState
Application.Calculate
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
Ken

Reply | Quote