How to check that Excel cell contains REF! error in VBA

If IsError(cell.Value) Then
    If cell.Value = CVErr(xlErrName) Then
        ...
    End If
End If

The original code is wrong and will give a Type Mismatch error if the cell does not contain an error.

If cell.Value = CVErr(xlErrRef) Then
    ...
End If


Sub CheckRef()
Dim CheckRange As Range, CheckCell As Range

    Set CheckRange = [A1:D10]                ' as per app

    For Each CheckCell In CheckRange
    If IsError(CheckCell) And _
       CVErr(CheckCell) = CVErr(2023) Then ' 2023 --> xlErrRef
            MsgBox ("#REF! in  " & CheckCell.AddressLocal)
            Exit Sub                     ' exit after first #REF! found
        End If
    Next CheckCell
End Sub

example

  • enter "=1/0" in B2 to create an error different to "#REF!"
  • enter 1 in B4, B5
  • enter "=B4+B5" in B7
  • delete row 4
  • run Sub CheckRef()

enter image description here

Tags:

Vba

Ref