Code for applying same change to range of existing formulas

I'd like to create a code that finds all "NA" values in my existing formulas, and replaces them with "'-"

I recorded a macros, where I erased NA and replaced with '-, which runs perfectly on a one-by-one scale. I also recorded a macros of this change to an entire row of my data, and this macros runs fine if I run the code on a line by line basis. If I highlight a larger range, it will revert the formula to the row that I originally selected. This would be fine, however it would still be highly time consuming over the course of my existing data.

Looking forward to your comments.

Thank you!

What about just modifying your formula to use IFERROR?

Looks like my last reply didn't go through!

Pardon the duplicate if it loads up in a few.

I'm trying to find a streamlined way to modify about 12,000 existing IF formulas, where the "value if false" is "NA". I am trying to change the "value if false" to '-

Thank you

Can you post the existing if formula? Are they the same formula's just copied down/across multiple sheets? Or various, multi condition?

If you can give a sample or example, it will help.

Found the world's best solution: Find NA and replace all with -

All the best

Can you post the existing if formula? Are they the same formula's just copied down/across multiple sheets? Or various, multi condition?

If you can give a sample or example, it will help.

Originally Posted by MontrealMTL

Found the world's best solution: Find NA and replace all with -

All the best

Are you sure that's what you wanted to do though? After doing a find and replace, didn't you just lose the formula you had there? What happens if the spreadsheet is updated?

I understand the Find and Replace method works reasonably well if you want to replace EVERY "NA" on the entire worksheet. I always get a little worried when using a find/replace with a string as generic as "NA".

Here's a piece of code that will replace "NA" with "-" in only the cells you have selected when you choose to run the macro. Hope this helps!

Please Login or Register  to view this content.