Applying IFNA to entire workbook

[Bral1]

Hi Everyone,

I hope you can help me, I have quite a large workbook with many many sheets and many formulas, it all works nicely although if anything comes out with an #n/a it throws a few things off. I could I suppose go through and manually wrap each formula with =IFNA(,0) but I was pretty sure there would be a way to wrap every formula wih it...?

If you could help you would save me time!

Someone here at work advised it can't be done but I hope you can prove him wrong.

Thank you so much for your responses x

[Debraj Roy]

Hi .. Welcome to the forum..

Isn't its nice to use a conditional Formatting..

Capture.JPG

BTW.. here is some (untested) code also..

http://www.excelitems.com/2011/03/wr...mulas-add.html
http://answers.microsoft.com/en-us/o...e-5f02ffa027c9

[martindwilson]

that wont get rid of the na() so it will still error on other calculations
anyway it would be easier to add iferror(XXXXXX,0) not ifna() else you'd have to
ifna(xxxx,0,XXXXX)
some code here to wrap all formuls in selection with iferror()
http://www.thespreadsheetguru.com/th...-your-formulas

[Bral1]

Hi Deb,

Thank you for your reply!

I amended the Macro to

Please Login or Register  to view this content.

How can I amend this to include Array formulas also? I tried changing the "And Not" to just "And" but it did not work! Any ideas?

[Debraj Roy]

Just a blind shot..

as I am not sure.. how to detect.. if multiple area has same array Formula, and not have a sample file to test..

Please Login or Register  to view this content.

[Bral1]

Thanks Mate!

One extra question... Is there a way to make it select the entire workbook instead of one sheet?