How to capture #REF! in Excel?

Question

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.

Answer 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")