I am doing a multiple criteria search using INDEX MATCH. The code works well to produce a formula, but what I really need it to do is return the row number when it finds the multiple match.
Sub new_forecast()
Set fSheet = Workbooks("TEST 2.xlsx").Worksheets("Product Split Filter")
Set nSheet = Workbooks("TEST.xlsm").Worksheets("2021 Forecast")
fSheet.Activate
fLastRow = ActiveSheet.Cells(65000, 1).End(xlUp).row
Rows("1:1").Select
With Selection
.Find(What:="fModel").Activate
fModCol = ActiveCell.Column
.Find(What:="Factory").Activate
fFacCol = ActiveCell.Column
.Find(What:="Sub_Region").Activate
fRegCol = ActiveCell.Column
.Find(What:="Units").Activate
fUnitCol = ActiveCell.Column
End With
nSheet.Activate
nLastRow = ActiveSheet.Cells(65000, 1).End(xlUp).row
Rows("1:1").Select
With Selection
.Find(What:="fModel").Activate
nModCol = ActiveCell.Column
.Find(What:="Factory").Activate
nFacCol = ActiveCell.Column
.Find(What:="Sub_Region").Activate
nRegCol = ActiveCell.Column
End With
fSheet.Activate
For fCast = 2 To fLastRow
fModel = fSheet.Cells(fCast, fModCol).Value
fFactory = fSheet.Cells(fCast, fFacCol).Value
fRegion = fSheet.Cells(fCast, fRegCol).Value
fUnits = fSheet.Cells(fCast, fUnitCol).Value
nSheet.Activate
Range(Cells(14 + fCast, 1), Cells(14 + fCast, 1)).FormulaArray = "=INDEX(T2:T10, MATCH(" & Chr(34) & fModel & fFactory & fRegion & Chr(34) & ", M2:M10 & N2:N10 & R2:R10, 0))" ' I really need this to just return the row number
Next fCast
End Sub
Bookmarks