I don't know of a formula that will return the cell address of a cell containing an error.
Someone else may have an answer.
As a result I have written a user defined function specifically to return the address of a cell
containing an error.
The function is FindErr()
It takes two arguments. The first is the range of cells to search and the second which is optional
and defaults to 1 is the incidence of a cell error.
If cells A1 to A10 contain two cells with errors, A5 and A8, then:
=FindErr(A1:A10) will return "A5"
=FindErr(A1:A10, 2) will return "A8"
If there is no error in the range of cells, FindErr returns the #NA error.
Here is the code for the function which should be placed in a standard module in your workbook:
Public Function FindErr(rngSearch As Range, Optional intErrOcc As Integer = 1) As Variant
Dim rngCell As Range
Dim n As Integer
n = 1
For Each rngCell In rngSearch.Cells
If IsError(rngCell.Value) Then
If n = intErrOcc Then
FindErr = rngCell.Address
n = n + 1
'No error found so return NA error
FindErr = CVErr(xlErrNA)
FindErr can be incorporated into other formulas.
Note that as this is a custom function and is in a module in the Workbook it will only be available
in that workbook, or copies of that workbook.