Hello all, I hope someone can help me again. I really appreciate it, and I've tried to explain it in more detail this
With the following procedure, I am using a Part ID number to look up that part's location in a worksheet. To
do this, I am using the DGET function which now works, thanks to all who helped with those issues.
But now I'm
trying to harness the outcomes from that function.
--It displays #VALUE! if the part ID number was not found in my worksheet and therefore the function can't return a
--It displays #NUM! if the part ID number was found in multiple records/rows in my worksheet and so it has multiple
I am trying to construct an IF section that will clarify what these mean to anyone
running this macro. I want to replace #VALUE! with "Not Found" and #NUM! with "Numerous".
The issue arises when I
compare the result of the function with the #VALUE! or #NUM! strings. I've tried StrComp and a simple equals sign (as below)
I've even tried having Option Compare at the top, though I'm not familiar with how that would change things. But I am getting
"Type Mismatch Error 13" all the time. I also get that error when set my DGET function = to a variable (Dim'ed as string)
I'm not seeing whats going on, hopefully I explained it alright.
Thanks in advance!
Variables to point out:
---Sheeti is a Publically defined Worksheet
---'CriteriaRange : Publically defined Range that is empty (until assigned criteria for my DGET function later within my
---'InventoryRange : Pubically defined Range that contains inventory data that I look through with my DGET function (Items in
the inventory are identifyable by a string I call "sItem" and each has a location in a corresponding column that I am looking
for with my DGET function)
Public Sub LoopThruParts()
Dim y As Integer
Dim sItem As String
Dim ValueError As String
Dim NumError As String
ValueError = "#VALUE!"
NumError = "#NUM!"
sItem = Sheeti.Cells(y, 6).Value
If sItem = "" Then
Sheeti.Cells(y, 2).Value = ""
CriteriaRange.Cells(2, 1).Value = "=""=" & sItem & """"
Sheeti.Cells(y, 2).Value = Application.DGet(InventoryRange, 3, CriteriaRange)
'The following line is where I get error 13
If Sheeti.Cells(y, 2).Value = ValueError Then
Sheeti.Cells(y, 2).Value = "Not Found"
ElseIf Sheeti.Cells(y, 2).Value = NumError Then
Sheeti.Cells(y, 2).Value = "Numerous"
y = y + 1
Loop Until IsEmpty(Sheeti.Cells(y, 5)) = True
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines