I have an Excel Worksheet that I am trying to pull a value from, based upon 2 values (Index - Match with 2 criteria) using range names as the lookup arrays.
But when I go to run the routine, I continue to get error 1004 - unable to access excel worksheetfunctions
Even when I modify the variables to use range locations and hard coded values, I get the same error.
I even tries to use a Vlookup - Match function set and I get the same error (for both the VLookup and Match).
Any ideas as to how to make this work
Sub IndexMatch()
Dim oEApp As Excel.Application
Set oEApp = CreateObject("Excel.Application")
Dim oExcelFilePath As String
oExcelFilePath = "C:\Users\asmen\Documents\MCC\Conveyor Masters\Belt Data.xlsx"
Dim oWB As Excel.Workbook
Set oWB = oEApp.Workbooks.Open(oExcelFilePath, False)
Dim oSheet_SP_CW_RW As WorkSheet
Set oSheet_SP_CW_RW = oWB.Sheets.Item("SPR_CW_RW")
Dim SPR_SeriesParamRange As String
SPR_SeriesParamRange = "SPR_Series"
Dim NumberSprocketsParamRange As String
NumberSprocketsParamRange = "Num_Sprockets"
Dim SPR_WidthParamRange As String
SPR_WidthParamRange = "SPR_Width"
Dim BeltSeriesValue As Integer
BeltSeriesValue = cb_BSeries.Value
Dim BeltWidthValue As Integer
BeltWidthValue = tb_BeltWidth.Value
Dim SPRSearch As Variant
If Not IsError(oEApp.WorksheetFunction.Index(NumberSprocketsParamRange, oEApp.WorksheetFunction.Match(BeltWidthValue, SPR_WidthParamRange, 0), oEApp.WorksheetFunction.Match(BeltSeriesValue, SPR_SeriesParamRange, 0))) Then
SPRSearch = oEApp.WorksheetFunction.Index(NumberSprocketsParamRange, oEApp.WorksheetFunction.Match(BeltWidthValue, SPR_WidthParamRange, 0), oEApp.WorksheetFunction.Match(BeltSeriesValue, SPR_SeriesParamRange, 0))
MsgBox SPRSearch
End Sub
Thank you in advance.
