Geek Answers Handbook

How to capture #REF! in Excel?

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

I can catch an 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 this?

Thanks.

+4
source share
4 answers

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

this works to identify only errors isref = IFERROR (IF (ERROR.TYPE (A1) = 4, "Return error", A1), "Everything is GOOD")

+3
source

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

Maybe something like:

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

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

I move on to using IsErr instead of IsError to ignore errors like N/A

+2
source

You can use the IF(ISERROR(ActiveCell),"Error") formula IF(ISERROR(ActiveCell),"Error")

However, this will lead to other errors.

+1
source

=IF(ISERROR(A1),ERROR.TYPE(#REF!)=ERROR.TYPE(A1),FALSE)

Returns true on #ref, false for all other errors or without errors.

+1
source

More articles:


All Articles