# Convert ISERROR formulas to IFERROR formulas [macro]

Share

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:

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### CP05: Interview with MrExcel – Bill Jelen (on his incredible work ethic)

Bill Jelen is one of my most favorite people on earth. That is why I wanted to have him as my first guest when I restarted the podcast. Even though I recorded this few weeks ago, only now I got around to publishing it. Please enjoy the conversation with Bill.

## Related Tips

Excel Howtos

VBA Macros

Excel Howtos

### Show difference between cells in status bar – VBA Example

Charts and Graphs

### 20 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"))

17. Steven_putty says:

This Excel IFERROR, ISERROR, ISERR, IFNA and ISNA Tutorial is accompanied by an Excel workbook containing the data and formulas I use in the examples below. You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter.
By the way! The best essay writing service - https://www.easyessay.pro/
And Happy New Year!

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.