UDF not returning correct information

[jjherrera]

For some reason my UDF only returns a number if the "CustPartNo"
variable is in the VOEMprodn range; otherwise it gives #VALUE!.
However I need to address the case(s) that the CustPartNo variable is
not contained in that range.

I keep looking at it and I can't seem to find where it goes wrong. Any
suggestions?

Here's my code . . .


Option Explicit

Function SHIPREQ(CustPartNo As Range, tDate As Range)
Dim VOEMprodn As Range
Dim VOEMSPO As Range
Dim HOEMprodn As Range
Dim HOEMSPO As Range
Dim ROEMprodn As Range
Dim ROEMSPO As Range

Dim MatchVprodn As Integer
Dim MatchVSPO As Integer
Dim MatchHprodn As Integer
Dim MatchHSPO As Integer

Set VOEMprodn = Workbooks("OEM Shipping
Schedule.xls").Worksheets("Production").Range("A4:A150")
Set VOEMSPO = Workbooks("OEM Shipping
Schedule.xls").Worksheets("Service Parts").Range("A4:A150")
Set HOEMprodn = Workbooks("OEM Shipping
Schedule.xls").Worksheets("Production").Range("A4:AH4")
Set HOEMSPO = Workbooks("OEM Shipping
Schedule.xls").Worksheets("Service Parts").Range("A4:AH4")
Set ROEMprodn = Workbooks("OEM Shipping
Schedule.xls").Worksheets("Production").Range("A4:AH150")
Set ROEMSPO = Workbooks("OEM Shipping
Schedule.xls").Worksheets("Service Parts").Range("A4:AH150")

MatchVprodn = Application.WorksheetFunction.Match(CustPartNo,
VOEMprodn, 0)
MatchHprodn = Application.WorksheetFunction.Match(tDate, HOEMprodn,
0)
MatchVSPO = Application.WorksheetFunction.Match(CustPartNo,
VOEMSPO, 0)
MatchHSPO = Application.WorksheetFunction.Match(tDate, HOEMSPO, 0)

'OLD DEBUG TEST STUFF
'SHIPREQ = Application.WorksheetFunction.CountIf(HOEMprodn, tDate)
'SHIPREQ = Application.WorksheetFunction.Index(ROEMprodn, 5, 5)
'<works>
'SHIPREQ = Application.WorksheetFunction.Index(ROEMprodn,
MatchVprodn, MatchHprodn)

If Application.WorksheetFunction.CountIf(HOEMprodn, tDate) > 0 Then
'If date is in HOEM set'
If Application.WorksheetFunction.CountIf(VOEMprodn, CustPartNo)

0 _

And Application.WorksheetFunction.CountIf(VOEMSPO,
CustPartNo) > 0 = True Then 'and If # is in prodn & SPO set'
SHIPREQ = Application.WorksheetFunction.Index(ROEMprodn,
Application.WorksheetFunction.Match(CustPartNo, VOEMprodn, 0),
Application.WorksheetFunction.Match(tDate, HOEMprodn, 0)) _
+ Application.WorksheetFunction.Index(ROEMSPO,
Application.WorksheetFunction.Match(CustPartNo, VOEMSPO, 0),
Application.WorksheetFunction.Match(tDate, HOEMSPO, 0)) 'return reqs
from prodn and SPO'

ElseIf Application.WorksheetFunction.CountIf(VOEMprodn,
CustPartNo) > 0 Then 'else If # is in prodn set'
SHIPREQ = Application.WorksheetFunction.Index(ROEMprodn,
Application.WorksheetFunction.Match(CustPartNo, VOEMprodn, 0),
Application.WorksheetFunction.Match(tDate, HOEMprodn, 0))
ElseIf Application.WorksheetFunction.CountIf(VOEMSPO,
CustPartNo) > 0 Then 'else If # is in SPO set'
SHIPREQ = Application.WorksheetFunction.Index(ROEMSPO,
Application.WorksheetFunction.Match(CustPartNo, VOEMSPO, 0),
Application.WorksheetFunction.Match(tDate, HOEMSPO, 0)) 'return reqs
from prodn'
Else
SHIPREQ = 0 'if # not in prodn or SPO set return 0'
End If
Else
SHIPREQ = 0 'if date not in set return 0'
End If

End Function

 

[keepITcool]

try like:


Function SHIPREQ(CustPartNo As Range, tDate As Range) As Double
Dim rPro As Range, rSpo As Range
Dim rowPro, colPro, rowSpo, colSpo 'variants

With Workbooks("OEM Shipping Schedule.xls")
Set rPro = .Worksheets("Production").Range("A4:AH150")
Set rSpo = .Worksheets("Service Parts").Range("A4:AH150")
End With

'use a variant to catch Match result
'use application w/o worksheetfunction to avoid R/T errors
With Application
rowPro = .Match(CustPartNo, rPro.Columns(1), 0)
colPro = .Match(tDate, rPro.Rows(1), 0)
rowSpo = .Match(CustPartNo, rSpo.Columns(1), 0)
colSpo = .Match(tDate, rSpo.Rows(1), 0)
End With

If Not IsError(rowPro) And Not IsError(colPro) Then
SHIPREQ = rPro(rowPro, colPro).Value
End If

If Not IsError(rowSpo) And Not IsError(colSpo) Then
SHIPREQ = SHIPREQ + rSpo(rowSpo, colSpo).Value
End If

End Function