Convert ISERROR formulas to IFERROR formulas [macro]

Posted on March 14th, 2011 in VBA Macros - 19 comments

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?

If you just want to examine the 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"))`

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:

Introducing our Online Power BI Class:

Would you like to join me on a date with Power BI? In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date.

 IFERROR Excel Formula – What is it, syntax, examples and howto Use Analytical Charts to Make your Boss Love You
 Written by Chandoo Tags: advanced excel, downloads, errors, excel 2007, if() excel formula, iferror, iserror(), Learn Excel, macros, Microsoft Excel Formulas, VBA Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

19 Responses to “Convert ISERROR formulas to IFERROR formulas [macro]”

1. ASHISH JAIN says:

awesome - Loved it đź™‚

Just the medicine that the doctor ordered. Thanks. I'm feeling better already.

3. Ganesh Khedkar says:

thanks a lot..

4. PATNAIK says:

Hai chandoo.com sir,
i am patnaik from vijayawada working Rlys.i am very happy to know about your chandoo(excel) site through my friend jagadeesh. i felt thrill to know your 95 excel tips.but i am little bit of dissapointed about FUNCTIONS which are not in very brief.
so i requesting you to send me about particularly " IF " functions, i hope you might help me in this.it is very much useful in my office section.

5. V S VENKATRAMAN says:

Dear Chandoo

Even after learning so many tips and tricks from your blog... I still feel that I am not fully conversant with the usage of excel. The basic reason is that I don't understand this VB language and the macros used in excel. In one of my post Mr Hui and Mr Vijay had given me a VB code for use in my data.

Since, I am not understanding the usage of this... can you please explain or alternatively suggest me as to where I can understand the basic concepts of usage of macros and VB in excel.

I am sure that you'll guide me in this regard.

Thanking you in anticipation

With regards

Venkat

6. Rick Rothstein (MVP - Excel) says:

@Venkat,

See if this tutorial helps you any...

http://www.jlathamsite.com/Teach/VBA/ProgrammingInExcelVBA_AnIntroduction.pdf

7. Jey says:

Hi Chandoo,
The macro to convert IFERROR formulas to IF(ISERROR) seems amazing.
However, it does not seem to work on my PC unfortunately.
When I try to run the code you provided, I have a "Syntax error" message that pops up and the following 2 lines are highlighted in red:
Attribute VB_Name = "Module1"
Public Const paramSeparator = ","

Do you have any idea how to solve this issue.
You help would be highly appreciated since I need to convert a full model...

• Chandoo says:

@Jey... Remove the first line Attribute VB_Name = â€śModule1? from your module. Also, make sure `Public Const paramSeparator = ","` has normal quotation marks, not dirty quotes.

Also, set the paramSeparator if you are using European version of excel where ; is the separator.

8. Jey says:

@ Chandoo
Thank you. I am using a European version of excel indeed I guess but the formula separator is , as well.
I have removed the first line and now have the following error message highlighting the word "Const":
"Constants, fixed-length strings, arrays, user-defined types and declare statements not allowed as Public members of object modules."
Any idea what to do?
Thanks a lot again.

9. Solene says:

Hi Chandoo,
I seem to have the same problem as Jey.
Even after removing the first line, it tells me constants etc. are not allowed as public members of object modules.
Do you know how to solve this?
Best

10. GE says:

This is the greatest thing ever to have been created!!!!

You will not believe the days and weeks worth of work this has saved me

Hi Chandoo,

Firstly, great site. Wish I had the time to make my site half as useful.

A couple of years ago I did the same thing for converting IFERROR to IF(ISERROR). It supports recursion, so should replace all IFERRORs.

http://www.professionalexcel.com/index.shtml?tips_01.html

I haven't looked at it in a long time, but thought you might be interested.

Chris
ProfessionalExcel.com

12. [...] Convert ISERROR formulas to IFERROR using VBA [...]

13. Juan Serrano says:

Chandoo, thank you so much.

I tried sharing a workbook with a client, but all he got where #NAME messages. I found the reason on the iferror formulas, and with the help of your macro I just converted 1302 instances to old-fashioned if(iserror()).

You saved me so much time, thank you!

14. [...] Converting Formulas using VBA [...]

15. Rana says:

Need some urgent help.

1 â€“ I need a print VBA, that will help me print specific cell range from specific tabs in the same worksheet. Basically I have created a form and want the user to click print on the last page and it should print only specific cell range in sheets in the same workbook.

The code I am using is mentioned below, however how do I select cell range in Sheet 1, 2, 3 & 4)
-----------------------------
Sub Printnew()
'' Printnew Macro
'
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")).Select
Sheets("Sheet1").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=True, Collate:=True, _
IgnorePrintAreas:=False
Sheets("Sheet1").Select

End Sub
----------------------------------------------------------------------
2 â€“ On sheet one I have included a add row macro. In this case each row has a formula and the add row macro adds a row and includes the formula. I donâ€™t want the user to see the formula. But if I hide and protect the macro doesnâ€™t work as it freezes and doesnâ€™t add a row..

16. Barry says:

Hi, I am using IF formula within a SUM formula, I altered it to convert the below formula, however the one problem I have is that there is an additional closing bracket in the formula. before the ,

------------------------------------------------
Formula in sheet
------------------------------------------------
=SUM(IF(ISERROR((P_C_E_Contacts!\$AG\$7:\$AG\$10000=\$A\$2)*(P_C_E_Contacts!\$AJ\$7:\$AJ\$10000=\$B5)*(P_C_E_Contacts!\$AH\$7:\$AH\$10000=\$A\$4)*(P_C_E_Contacts!\$AI\$7:\$AI\$10000=\$D\$3)*(P_C_E_Contacts!\$AL\$7:\$AL\$10000)),"Error",((P_C_E_Contacts!\$AG\$7:\$AG\$10000=\$A\$2)*(P_C_E_Contacts!\$AJ\$7:\$AJ\$10000=\$B5)*(P_C_E_Contacts!\$AH\$7:\$AH\$10000=\$A\$4)*(P_C_E_Contacts!\$AI\$7:\$AI\$10000=\$D\$3)*(P_C_E_Contacts!\$AL\$7:\$AL\$10000))))
----------------------------------------------

I have tried to modify further but its a bit above my understanding I think.

Any advice would be greatly appreciated.

Thanks,

Barry

• Barry says:

Sorry, something happened when I posted.

The additional bracket is between â€“->))<--,"Error"))

 IFERROR Excel Formula – What is it, syntax, examples and howto Use Analytical Charts to Make your Boss Love You