问题:

I would like to know how to capture the #REF! error in Excel.

I am able to catch error in vba with the following code :

    If WorksheetFunction.IsError(ActiveCell) Then
         If ActiveCell.Value = CVErr(xlErrRef) Then
              ActiveCell.Value = "Error"
         End If
   End If

I would like to write the same function in Excel Formula Bar. Any ideas how to do it?

Thanks.

回答1:

You can use ISERROR in the formula bar and check if true or false

this works to identify only isref errors =IFERROR(IF(ERROR.TYPE(A1)=4,"Ref Error",A1),"All GOOD")

回答2:

Note that the cell will only return the first error encountered when evaluating a formula, so if a cell has a #Name? error that occurs before a #Ref! error, there is no way to trap the potential #Ref! error.

Maybe something like:

=AND(ISREF(A1),ISERR(A1))

First test whether the cell contains a REFERENCE and then check to see if it also contains an error.

I revise to use IsErr instead of IsError to ignore N/A type errors.