Custom number format in Excel for error messages

[jdcrowley]

Hi everyone,

Is there a custom number format that will recognize whether a cell has an error (#NA or DIV0, etc ... ) and replace it with a text string?

I would like to prepare a table that looks nice. That means it can't have things like #NA in it. Errors have to be replaced with something pretty, like "...". But I don't want to type over my formulas with "..." because when I correct the input cell I will likely forget that I also have to go fix my table. I would prefer that Excel do this for me automatically.

I can do it with a formula that has something like =IF(ISERR(A15),"...", A15), but that is tedious to type into every cell. It would be much easier if I could just create a custom number format to solve the problem.

Thanks.

 

[erik.van.geit]

1.
you could use a button with some actions
make copy of sheet
hide it
rename it
visible sheet gets name of original
apply code to set errorformulas

when done click button to delete sheet
unhide original sheet and rename to original name

a bit overkill ?

2.
you could hide hide the formulas (see cellproperties/protection tab)
then protect sheet
no confusion about formulas since nobody can see them ...

 

[Legacy 55362]

Perhaps this :-

Sub Remove_ISERROR_From_Formulas()
Dim rng As Range, cell As Range, fmla As String, f%
If TypeName(Selection) <> "Range" Then
    MsgBox "Select one or more worksheet cells."
    Exit Sub
End If
On Error Resume Next
If Selection.Cells.Count = 1 Then
    If Selection.HasFormula Then
        If Left(Selection.Formula, 10) = "=IF(ISERROR" Then
            f = InStr(Selection.Formula, "...")
            fmla = Right(Selection.Formula, Len(Selection.Formula) - 1)
            Selection.Formula = "=" & Mid(Selection.Formula, f + 5, Len(Selection.Formula) - f - 5)
        End If
    End If
    GoTo e
End If
Set rng = Selection.SpecialCells(xlCellTypeFormulas, 23)
If rng Is Nothing Then GoTo e
For Each cell In rng
    If Left(cell.Formula, 10) <> "=IF(ISERROR" Then
        f = InStr(cell.Formula, "...")
        fmla = Right(cell.Formula, Len(cell.Formula) - 1)
        cell.Formula = "=" & Mid(cell.Formula, f + 5, Len(cell.Formula) - f - 5)
    End If
Next
e:
On Error GoTo 0
End Sub

 

[Greg Truby]

Actually, if I could change them back it would be a slight advantage since the next person who works on my table is going to freak out if they see that formula.
-jdcrowley

IMHO you're better off leaving them as formulae if the inputs will ever be altered back to statuses where you can expect errors again. If you want to keep the in-cell formulae looking as pretty as possible you can edit Erik's bit of code a bit to where it simply wraps the formulae in a wee UDF wrapper. Let's name our wrapper e() to keep it as short as possible. Taking Erik's code as our starting point:

Sub EditFormulae()
' Based on code from Erik van Geit
    Dim rngCell As Range, strFormula As String
    For Each rngCell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
        With rngCell
            strFormula = Mid(.Formula, 2)
            .Formula = "=e(" & strFormula & ")"
        End With
    Next rngCell
End Sub

Now your in-cell formulae simply read<ul>[*]=e(originalformula)[/list]The miniature wrapper UDF is just one line of code:

Public Function e(varInput As Variant) As Variant
    e = IIf(IsError(varInput), "...", varInput)
End Function

That would keep your in-cell formulae neat and tidy and relatively easy to edit should you need to do so.