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()