What I'm trying to accomplish here is to write a function in VBA that
essentially uses the excel functions index in conjunction with match to
search through matrices in two worksheets in another workbook (OEM
shipping schedule.xls) and return these values based on the inputs of a
"date" and a "part number" provided in my active worksheet.

If the part exists in both worksheets I want it to return the sum of
the values; if it only exists in one I want it to return only that
value; if it exists in none I want it to return 0.

At this point I keep getting a "subscript out of range" error.

Obviously this is based on a formula I created in excel that worked
with fewer "if" statements. But I was forced to try to learn some VBA
to overcome both the max cell character limit and the argument limit.

I tried using names to overcome the argument limit but that wouldn't
work as I want the variables "tDate" and "CustPartNo" to be relative
references so I can extend the formula to other cells in my
spreadsheet, however using names only gives me absolute references.

Please help if you can; like I said I've only just taken up VBA
programming two days ago . . .

Thanks in advance for any help . . .

Here is my code:

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

VOEMprodn = Workbooks("'OEM Shipping
Schedule.xls'").Worksheets("Production").Range("$A $4:$A$150")
VOEMSPO = Workbooks("'OEM Shipping
Schedule.xls'").Worksheets("'Service Parts'").Range("$A$4:$A$150")
HOEMprodn = Workbooks("'OEM Shipping
Schedule.xls'").Worksheets("Production").Range("$A $4:$AH$4")
HOEMSPO = Workbooks("'OEM Shipping
Schedule.xls'").Worksheets("Production").Range("$A $4:$AH$4")
ROEMprodn = Workbooks("'OEM Shipping
Schedule.xls'").Worksheets("Production").Range("$A $4:$AH$150")
ROEMSPO = Workbooks("'OEM Shipping
Schedule.xls'").Worksheets("Production").Range("$A $4:$AH$150")

If Application.WorksheetFunction.CountIf(VOEMprodn, tDate) 0 Then
'If date is in 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