Putting Index Match Array in VBA

[stlpony]

Hello!

Goal: Run an index match down the data in column #54 , based on the info in cell $A$2 and cell $A7.

1) I'm having trouble getting the VBA coding to reference the tab name that I'm working on without manually typing it in the coding. I'll need to run this macro on many different tabs.
2) How do I get the macro to do the CTRL+Shift+Enter for each cell?
3) I want A2 fixed in the formula, but I need A7 to go down as many rows as needed


Range(Cells(6, 54), Cells(LastRow, 54)).Formula = "=INDEX(BackData!$A$1:$G$90000,MATCH(ActiveSheet.Name!$A$2&ActiveSheet.Name!A7,BackData!$D$1:$D$90000&BackData!$B$1:$B$90000,0),3)"

Thanks!!

 

[stlpony]

looks like .FormulaArray fixes the Array question in #2

 

[Marcelo Branco]

To set an array formula in multiple cells is a little bit tricky. You should enter the formula only in first cell of the range and then fill down or copy/paste.

Take a look at: (see topic 3.2)
https://colinlegg.wordpress.com/2012/05/23/working-with-range-formulaarray-in-vba/

M.