VBA Action on excel #Reference! error 4?

[Gunnar Johansson]

Hello,

I need to supervise a range of cells and if any of them make error
#Referense! I need to run a macro. I can't use Excel GUI, it need to be in
VBA.

Can any of you suggest a code to handle this?

/Thank you

 

[kkknie]

How about:

Code
-------------------
Sub TestRefErr()

Dim r as Range
Dim bErr as Boolean

bErr = False

For Each r in Range("A1:B10")

If CStr(r.Value) = "Error 2023" Then
bErr = True
Exit For
End If

Next

If bErr = True Then Call YourMacroName

End Su

 

[Dave Peterson]

You could also look at what's displayed in the cell.

dim myCell as range
dim myRng as range

with activesheet
set myrng = .range("a1",.cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
if lcase(mycell.text) = "#ref!" then
'do your thing
else
'no error
end if
next mycell