Forum Discussion
Macro for an array formula
I have a product sales spreadsheet which is generated monthly. I also have a rebate report which is also generated monthly. I inserted an array formula into a new column (column E) in the sales sprea...
Try using something like this. Not sure how you plan on inserting rows....but this will get you the array formula via VBA. You'll need to define the last row of your data so the formula extends the correct length:
Sub Test()
'Define Lrow based on data in column A
Lrow = Worksheets("Rebate Report").Cells(Rows.Count, "A").End(xlUp).Row
'Enter array formula based on lastrow
Range("E2:E" & Lrow).FormulaArray = "=INDEX('Rebate Report'!$A$4:$L$" & Lrow & ",MATCH(1,('Rebate Report'!A:A=B2)" _
& "*('Rebate Report'!B:B=C2)*('Rebate Report'!C:C=D2),0),1)"
End Sub
Thanks!
