Replacing an ERROR message with "NA"
 

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?

Replacing an ERROR message with "NA"
 

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)
--
Kevin Backmann


"COL" wrote:

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?

Replacing an ERROR message with "NA"
 

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)


"COL" wrote in message
...
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?

Replacing an ERROR message with "NA"
 

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.

"COL" wrote:

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?

Replacing an ERROR message with "NA"
 

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


On Thu, 13 Jul 2006 12:44:01 -0700, COL wrote:

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?