Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

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?

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.

Convert ISERROR formulas to IFERROR formulas and vice-a-versa - Excel Macros

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:

    Your email address is safe with us. Our policies

    Written by Chandoo
    Tags: , , , , , , , , , ,
    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 :)

    2. Ninad Pradhan says:

      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…
      Thank you in advance.

      • 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

    11. Chris Read says:

      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. 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!

    13. 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..

      I have explored all horizons and done my research but can’t crack the above two. Please please help me with the above.

    14. 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 ,

      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

    Leave a Reply