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





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


wrote :

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 . . .