Convert ISERROR formulas to IFERROR formulas [macro]

Last Friday, we have learned about an interesting formula – IFERROR Formula using which you can easily handle errors in Excel workbooks.

Quite a few people reading that page asked, “Wow, this is good. But how can I take a sheet full of =IF(ISERROR(…)….) formulas and convert them to =IFERROR()”

There is a different set of folks who asked “Wow, this is good. But quite a few of my colleagues use Excel 2003 and they see a bunch of #NAME errors when I send them an excel workbook with IFERROR formulas. Any help?!?”

I am pleased to announce that I wrote 2 simple macros, iferror2iserror() and iserror2iferror() that would scan formulas in a bunch of selected cells and convert them from IFERROR to ISERROR and vice-a-versa. Pretty cool, eh?

Download Excel Macros Workbook

Click here to download the workbook that has macros to convert IFERROR formulas to ISERROR formulas and vice-a-versa.

If you just want to examine the code:

Click here to view the VBA Module code.

What are these macros and how do they work?

The workbook contains 2 macros – iferror2iserror() & iserror2iferror().

What does iferror2iserror() macro do?

As the name suggests, It scans a bunch of selected cells for any IFERROR formulas and then converts them to ISERROR formulas.

For eg. if a cell has =IFERROR(expression, error), the output would be =IF(ISERROR(expression),error,expression)

What does iserror2iferror() macro do?

This macro scans a bunch of selected cells for any ISERROR formulas and then converts them to IFERROR formulas.

For eg. if a cell has =IF(ISERROR(expression),error,expression), the output would be =IFERROR(expression, error)

How to use these macros?

Very simple. Just select the cells with formulas and then run the required macro. The macros only affect cells with either IFERROR or ISERROR formulas.

What are the limitations of these macros?

These macros should hold good for many real life scenarios. That said,

1. These macros do not check for IFERROR (or ISERROR) recursively. ie, if a formula has IFERROR inside another IFERROR, only the first one would be converted.
2. These macros do not work when you have commas (,) inside the formula in double quotes. For eg. the below formula fails.
   =IFERROR(VLOOKUP("Kirk, James",tblStarwars,2,false),"Captain not found"))

Your comments:

How do you convert IFERROR or ISERROR formulas? Do you use a macro or you manually change the formulas? Please share your techniques and ideas using comments.

Also, if you wish to modify the code, please feel free to do so. Share your work with rest of us thru comments so that we can benefit too.

Get more Macro examples:

• Removing page break lines with a macro
• Print Excel Reports via Word
• Merge cells without loosing data
• Add a range of text values – CONCAT() UDF
• More on Macros & Formula Errors