Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
question about excessive code in replacing #N/A when using VLOOKUP | Excel Discussion (Misc queries) | |||
How do I replace an asterisk without replacing everything? | Excel Discussion (Misc queries) | |||
replacing #N/A values with zero values | Excel Worksheet Functions | |||
Stopping autoformat when replacing cells. | New Users to Excel | |||
XL 2003: Replacing X-Axis Labels with Custom Ones... | Charts and Charting in Excel |