Replacing an ERROR message with "NA"

[Guest]

I have created a table and some of the cells display the message "#DIV/0"
which I expected would happen, yet I want to automate the document so that
all error messages are replaced by the text message "NA". It's a large
document and it would take a very long time to change each cell manually so I
was hoping someone could help me. I also update it often, therefore I need to
keep the formula in the cell and if I type NA in the cell the formula will be
erased...

Does anybody know how to fix this?

 

[Guest]

You can use this generic IF statement to work around errors:

IF(ISERROR(Formula),"N/A",Formula)

If you're dividing A1 by B1 the IF statement would read:

IF(ISERROR(A1/B1),"N/A",A1/B1)

 

[John P]

If I use a formula that has expected errors in it I use ISERROR.

Example

I am dividing cell d2/c2 and an error is expected I will use the formula

+if(iserror(d2/c2),Response I want,d2/c2)

 

[Guest]

You could augment your existing formula with a test for a 0 denominator. For
instance, if you've got =(a1/b1), you could make that =if(b1=0,"NA",a1/b1).
Then you've got a formula that will update automatically along with a means
to replace the #DIV/0 error.

 

[Gord Dibben]

To change all at once use this macro after selecting your used range on the
sheet.

It will ignore cells without formulas.

Sub ErrorTrapAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISERROR*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISERROR(" & myStr & "),""NA""," & myStr & ")"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP