Ref error type - VBA

[Kavy]

Hello all,

My question is Is there any line of code that looks for just "#Ref!" errors?

We have an excel sheet which is a roll-up of other excel workbooks. The workbooks are named after their "epr" number.

If a user enters a epr number, the macro checks the directory for the epr number b4 grabbing data from the sheet.

How it checks the directly is it actually trys to pull data, and if the first peice of data comes back as a "#REF!" then it assumes theres no file.

I was using "Iserror" to check this, but it turns out that line of code also checks for permissions, and that snaged us.

Is there any line of code that looks for just "#Ref!" errors?

I tried "CVErr(xlErrRef)" but I can't seem to figure out the syntax, and help files no help
=ERROR.TYPE(D12) seems to only work in an excel cell not in VBA??

Thank you!

 

[Richard Schollar]

Are you physically looking for a cell with a #REF! error within it? If so, you could use the Find() method of the range object?

 

[pgc01]

Also you can loop through your cells, like:

Sub RefErr()
Dim r As Range
 
For Each r In Range("A1:A5")
    If IsError(r) Then If r = CVErr(xlErrRef) Then MsgBox "Range " & r.Address & " has a #REF! error."
Next r
End Sub

 

[Kavy]

Thank you both for your replys, the r = CVErr is what I was tryign to figure out!

When my excel cell referances another file which isnt there, it loads a "open" window. In VB is there anyway to turn off that window?

Thank you!

 

[Kavy]

Got it
application.displayalerts = false baby!

lol thanks again!