Trying to reference ranges in another workbook using index in conjunction with match

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

Some of your worksheet functions require range references - make sure they
are ranges and not strings. This is critical in Index(), Match(), Lookup(),
and many others...

If rng = "A1" then it is a string and you must use Range(rng)
If set rng = Range("A1") then rng is a range and you can use rng

double check all your variables for type and make sure that you are using
them per type.

hope this helps...

--
steveB

Remove "AYN" from email to respond
<[email protected]> wrote in message
news:[email protected]...
> 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
>

Didn't help me initially, but I got it working finally by decomposing
my code, seeing if the pieces worked, and then putting them together as
a whole.

I also learned how to use the newsgroups better so I could research my
particular problems rather than have to wait until someone came to my
rescue.

Thank you though for your respeonse; it did get me pointed in the right
direction!

CODE:

Option Explicit

Function SHIPREQ(CustPartNo As String, tDate As Variant)
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 = MatchHprodn
'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

There's a great tool that you can get. Helps to find all
kinds of helpful stuff....

Google Search add-in

http://www.rondebruin.nl/Google.htm

--
steveB

Remove "AYN" from email to respond
<[email protected]> wrote in message
news:[email protected]...
> Didn't help me initially, but I got it working finally by decomposing
> my code, seeing if the pieces worked, and then putting them together as
> a whole.
>
> I also learned how to use the newsgroups better so I could research my
> particular problems rather than have to wait until someone came to my
> rescue.
>
> Thank you though for your respeonse; it did get me pointed in the right
> direction!
>
> CODE:
>
> Option Explicit
>
> Function SHIPREQ(CustPartNo As String, tDate As Variant)
> 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 = MatchHprodn
> '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
>