ProfessionalExcel.com In the office, Excel is a way of life… not just a tool

23Apr/136

Replace IF(ISERROR()) with value_if_false argument

This tip relates to the technique of trapping and handling errors in a formula. Prior to Excel 2007 (e.g. Excel 2003, 2002, 2000), this was accomplished using a combination of the IF and ISERROR functions to test for an error in a formula, e.g. =IF(ISERROR(MyFormula),ReturnSomethingElse,MyFormula).

A reader of my blog was after some code to do something a little different to a previous post which looked at replacing the Excel 2007 IFERROR function with IF and ISERROR.

The slight twist on this was when you wanted to get rid of the error trapping (IF and ISERROR) and just have the original formula, warts (errors) and all!

In the VBA code below, I have developed a handy procedure (ReplaceIFISERRORCellFormulas) that will convert a range of selected cells that contain IF(ISERROR()) formulas to instead display the formula used in the 'value_if_false' argument of the original IF(ISERROR()) function.