Error.Type or IsError to trap #VALUE! and #NUM!

[Ms. AEB]

I am using the DGET worksheet function to lookup values and I want to display
text explaining the #VALUE! and #NUM! responses as "Item not found" and
"Numerous Items Found".

If I trap it with IsError it will return one value for all of them, and so I
wanted to use Error.Type that I found in a 2002 manual. I'm using 2003 and I
can't find this function anywhere in my object browser or help screen. Does
it exist? How can I differentiate between these two?

Heres my code if it helps:
'Note= Sheeti, InventoryRange, CriteriaRange, and y are all defined variables

Sheeti.Cells(y, 2).Value = Application.DGet(InventoryRange, 3,
CriteriaRange)

If Error.Type(Sheeti.Cells(y, 2)) = 3 Then
Sheeti.Cells(y, 2).Value = "Not in Stock"
ElseIf Error.Type(Sheeti.Cells(y, 2)) = 6 Then
Sheeti.Cells(y, 2).Value = "Numerous"
End If

 

[Jim Cone]

"Error.Type" is an Excel function used on the worksheet.
You need to use the CVErr function to determine the type of error
existing in a worksheet cell...

Dim vReturn as Variant
If IsError(Sheeti.Cells(y, 2).Value) Then
vReturn = Sheeti.Cells(y, 2).Value

Select Case vReturn
Case CVErr(xlErrDiv0)
Sheeti.Cells(y, 2).Value = "Bad"
Case CVErr(xlErrNA)
Sheeti.Cells(y, 2).Value = "Awful"
Case CVErr(xlErrName)
Sheeti.Cells(y, 2).Value = "Stinks"
Case CVErr(xlErrNull)
Sheeti.Cells(y, 2).Value = "Worse"
Case CVErr(xlErrNum)
Sheeti.Cells(y, 2).Value = "Terrible"
Case CVErr(xlErrValue)
Sheeti.Cells(y, 2).Value = "Not even close"
Case Else
Sheeti.Cells(y, 2).Value = "Oops!"
End Select

End If
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Ms. AEB"
<Ms. (e-mail address removed)>
wrote in message
I am using the DGET worksheet function to lookup values and I want to display
text explaining the #VALUE! and #NUM! responses as "Item not found" and
"Numerous Items Found".

If I trap it with IsError it will return one value for all of them, and so I
wanted to use Error.Type that I found in a 2002 manual. I'm using 2003 and I
can't find this function anywhere in my object browser or help screen. Does
it exist? How can I differentiate between these two?

Heres my code if it helps:
'Note= Sheeti, InventoryRange, CriteriaRange, and y are all defined variables

Sheeti.Cells(y, 2).Value = Application.DGet(InventoryRange, 3,
CriteriaRange)

If Error.Type(Sheeti.Cells(y, 2)) = 3 Then
Sheeti.Cells(y, 2).Value = "Not in Stock"
ElseIf Error.Type(Sheeti.Cells(y, 2)) = 6 Then
Sheeti.Cells(y, 2).Value = "Numerous"
End If