Excel IFERROR Function
The Excel IFERROR function returns a specified value if the first value in the IFERROR formula returns an error, otherwise it will return the standard result
Example: Excel IFERROR Function
|
Result in cell D5 (CHECK) - returns the specified value (CHECK) given that the formula in the IFERROR function returned an error.
|
|
=IFERROR(B6/C6,"CHECK")
|
Result in cell D6 (CHECK) - returns the specified value (CHECK) given that the formula in the IFERROR function returned an error.
|
|
=IFERROR(B7/C7,"CHECK")
|
Result in cell D7 (5) - returns the result of the formula in the IFERROR given the formula does not return an error.
|
METHOD 2. Excel IFERROR function using the Excel built-in function library
EXCEL
Formulas tab > Function Library group > Logical > IFERROR > populate the input boxes
| =IFERROR(B6/C6,"CHECK") Note: in this example we are dividing the value in cell (B6) by (C6) which returns an error. Therefore . |
 |
Sub Excel_IFERROR_Function()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("IFERROR")
'apply the Excel IFERROR function
ws.Range("D5") = Application.WorksheetFunction.IfError(Application.VLookup("d", ws.Range("B5:C7"), 2, False), "CHECK")
ws.Range("D7") = Application.WorksheetFunction.IfError(ws.Range("B7") / ws.Range("C7"), "CHECK")
ws.Range("D5") = Application.WorksheetFunction.IfError(Application.VLookup("d", ws.Range("B5:C7"), 2, False), "CHECK")
ws.Range("D7") = Application.WorksheetFunction.IfError(ws.Range("B7") / ws.Range("C7"), "CHECK")
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named IFRROR.
Worksheet Name: Have a worksheet named IFRROR.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("D5") and ("D7") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Output Range: Select the output range by changing the cell references ("D5") and ("D7") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
ADDITIONAL NOTES
Note 1: The VBA code cannot process a formula that is divided by 0, which is the formula in cell ("D6"). To avoid this use On Error Resume Next.
DESCRIPTION
The Excel IFERROR function returns a specified value if the first value in the IFERROR formula returns an error, otherwise it will return the standard result.
The Excel IFERROR function returns a specified value if the first value in the IFERROR formula returns an error, otherwise it will return the standard result.
SYNTAX
=IFERROR(value, value_if_error)
=IFERROR(value, value_if_error)
ARGUMENTS
value: (Required) The value (formula) that is to be tested for errors.
value_if_error: (Required) The value that is returned if an error is identified.
value: (Required) The value (formula) that is to be tested for errors.
value_if_error: (Required) The value that is returned if an error is identified.
ADDITIONAL NOTES
Note 1: The IFERROR function was implemented in Excel 2007 and onward. The earlier Excel version will not have the function.