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,
- 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.
- 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.
20 Responses to “Convert ISERROR formulas to IFERROR formulas [macro]”
awesome - Loved it 🙂
Just the medicine that the doctor ordered. Thanks. I'm feeling better already.
thanks a lot..
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.
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
@Venkat,
See if this tutorial helps you any...
http://www.jlathamsite.com/Teach/VBA/ProgrammingInExcelVBA_AnIntroduction.pdf
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...
Thank you in advance.
@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.
@ 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.
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
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
[...] Convert ISERROR formulas to IFERROR using VBA [...]
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!
[...] Converting Formulas using VBA [...]
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..
I have explored all horizons and done my research but can’t crack the above two. Please please help me with the above.
@Rana
Have a look at : http://chandoo.org/wp/2011/09/14/hui%E2%80%99s-excel-report-printer/
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 ,
additional bracket between -->)<--,"Error"))
------------------------------------------------
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
Sorry, something happened when I posted.
The additional bracket is between –->))<--,"Error"))
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!