| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trying to reference ranges in another workbook using index in conjunction with match
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 wrote in message oups.com... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trying to reference ranges in another workbook using index in conjunction with match
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trying to reference ranges in another workbook using index in conjunction with match
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 wrote in message oups.com... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing part of cell reference in INDEX - MATCH formula | Excel Worksheet Functions | |||
| INDEX - MATCH - OFFSET By Reference | Excel Worksheet Functions | |||
| External Reference with INDIRECT, INDEX/MATCH | Excel Worksheet Functions | |||
| Index match formula works in for one worksheet reference but not another | Excel Worksheet Functions | |||
| INDEX - MATCH - VLOOKUP - returning missing reference | Excel Discussion (Misc queries) | |||
Hybrid Mode
