Replacing #N/A with text or 0

[N-ter]

An Edit -> Replace would be excellent approach.

Another less used method... Highlight all data, Edit -> GoTo -> Special -> Constants -> check Errors and uncheck everything else -> OK -> enter 0 and press CTRL + ENTER

I wonder if there is any formula that does the same thing for all the #N/A errors in the file, ie search and replace all, without modifying their original formulas or conditioning the cells causing the error. The range where the errors are is B22:J82 so it's not just a column or a row.

(sorry for my previous incursion)

[DonkeyOte]

I think you need to elaborate - if you're saying you want to remove #N/As without removing and/or adjusting formulae then no, unless you use Conditional Formatting to mask the error (implication is you don't want that either)

Best way forward would be to post a sample file to illustrate what you have and what you want.

[N-ter]

I have input values (yellow column) and output values. When one (or more) of the input values is 0, some output values show "#N/A". I want to mask the error so that it will show 0 or a text. But I want that if I change back the input 0 with another value, the output value will be calculated and shown again.

Modifying the formulas requires a lot of time because there are many cells.

[DonkeyOte]

As outlined you could rather than modify formulae apply Conditional Formats to the ranges which mask the appearance of the Errors
However, Conditional Formatting is super volatile so this may not be a wise choice pending volume of calcs / Calc Mode etc... and obviously underlying error values persist.

Adding handlers to the formulae manually can be time consuming but given use of XL2007+ you could add a quick IFERROR handler via code, in basic terms:

Please Login or Register  to view this content.

the above adds an IFERROR handler to all functions on the active sheet if not already in existence - you can modify C_Default to whichever value you want to appear in cases of error

you will find countless (better) examples of this type of thing on line however

Again, not saying I would do this myself but it might prove to be the lesser of two evils if you have masses of formulae already configured.

edit: above revised at 12:47 UK time to cater for varying locales (reverted to FormulaLocal and use of International Column Delimiter)

[N-ter]

So there's no elegant and automatic way that could work for both XL2003 and 2007 versions... Thanks anyway DonkeyOte.

[DonkeyOte]

Well, yes, you can revert to ISERROR and double evaluation but it's obviously less efficient (but backwards compatible).

alterations to prior code would be:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

other than that the code remains identical

(it would make sense to store the xlColumnSeparator as a variable rather than repeat the lengthy call twice over)

[patatvs]

What about (in your cells b12:b21, for example) something like:

Please Login or Register  to view this content.

...or...

Please Login or Register  to view this content.

[DonkeyOte]

@patatvs

Modifying the formulas requires a lot of time because there are many cells.

so here we are concerned with automating the adaptation of the various formulae c/o VBA.
(obviously the adaptation itself can be varied)