INDEX MATCH with two criteria - Automation error

Question

I'm trying to run an Index/Match query using WorksheetFunctions rather than Excel formulas.

The function indexes an arrea in exernal workbook (extwbk), and matches store number in column A of extwbk to store number col A in ActiveWorkbook, and month (sCurrMth) to months in row 1 of extwbk. Match Store#&Aug to come back with store accounting period in fin year

I declared variables, set some values but am getting an 'Run-time error '-2147221080 (800401a8) Automation error' when I get to

With fNameAndPath.Worksheets("Sheet1")

When I change from fNameAndPath.Worksheets("Sheet1") to ActiveWorkbook.ActiveSheet, I get the Run-time error '424' Object required on a different line

.Cells(rw, 10) = Application.WorksheetFunction.Index(x, Application.WorksheetFunction.Match(fNameAndPath.Range("A2"), extwbk.Worksheets("Info Sheet").Range("A1:A" & Rows.Count), 0), _ Application.WorksheetFunction.Match(sCurrMth, extwbk.Worksheets("Info Sheet").Range("1:1"), 0))

My code is as follows and I would appreciate any guidance on this

 Sub IndexMatch()
'Index/Match store info and get store current period
Dim rw As Long, x As Range
Dim extwbk As Workbook, fNameAndPath As Workbook
Dim sCurrMth As String
Dim rStoreNo As Range
Dim rMth


Set fNameAndPath = ActiveWorkbook
Set extwbk = Workbooks.Open("H:\***\***\Master Store Info.xlsm", ReadOnly:=True)
Set x = extwbk.Worksheets("Info Sheet").Range("A1:z9999")
sCurrMth = "Aug"
Set rStoreNo = extwbk.Worksheets("Info Sheet").Range("A1:A" & Rows.Count)

With fNameAndPath.Worksheets("Sheet1")
    For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
    .Cells(rw, 10) = Application.WorksheetFunction.Index(x, Application.WorksheetFunction.Match(fNameAndPath.Range("A2"), extwbk.Worksheets("Info Sheet").Range("A1:A" & Rows.Count), 0) + _
    Application.WorksheetFunction.Match(sCurrMth, extwbk.Worksheets("Info Sheet").Range("1:1"), 0))
    Next rw
End With
Workbooks("Master Store Info.xlsm").Close
End Sub

Ahh, thank you Rory! It worked, the only thing now is that it's looking up only the A2 value in fNameAndPath.Worksheets("Sheet1").Range("A2"). I would have thought Next rw would make it look in A3? — user8449681
@user8449681 Not unless you change fNameAndPath.Range("A2") to fNameAndPath.Range("A" & rw). Otherwise, since you've explicitly stated A2 in your code, it'll keep looking there. — dwirony

user8449681 user8449681 · Accepted Answer · 2017-08-21T15:34:31

Sub IndexMatch()
'Index/Match store info and get store current period
Dim rw As Long, x As Range
Dim extwbk As Workbook, fNameAndPath As Workbook
Dim sCurrMth As String
Dim rStoreNo As Range
Dim rMth


Set fNameAndPath = ActiveWorkbook
Set extwbk = Workbooks.Open("H:\***\***\Master Store Info.xlsm", ReadOnly:=True)
Set x = extwbk.Worksheets("Info Sheet").Range("A1:z9999")
sCurrMth = "Aug"
Set rStoreNo = extwbk.Worksheets("Info Sheet").Range("A1:A" & Rows.Count)

With fNameAndPath.Worksheets("Sheet1")
'Start in row2 and look until the end of rows
For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(rw, 10) = Application.WorksheetFunction.Index(x, Application.WorksheetFunction.Match(fNameAndPath.Worksheets("Sheet1").Range("A" & row), extwbk.Worksheets("Info Sheet").Range("A1:A" & Rows.Count), 0) + _
Application.WorksheetFunction.Match(sCurrMth, extwbk.Worksheets("Info Sheet").Range("1:1"), 0))
Next rw
End With
Workbooks("Master Store Info.xlsm").Close
End Sub

INDEX MATCH with two criteria - Automation error

1 Answers