In a VBA Module, I'm attempting to insert a formula in a range of cells, using this VBA code:
Sheets("allocation_data_temp").Range("Y2:Y" & Lastrow2).Formula = "=INDEX(StoreRanks!M:M,MATCH(1,('allocation_data_temp'!$E2=StoreRanks!$C:$C)*('allocation_data_temp'!$A2=StoreRanks!$A:$A),0))
However, when the macro runs, this is actually what is being inserted:
=INDEX(StoreRanks!M:M,MATCH(1,(allocation_data_temp!$E2=@StoreRanks!$C:$C)*(allocation_data_temp!$A2=@StoreRanks!$A:$A),0))
The @ symbols before the sheet names are causing the reference to error. Why is this function editing the string I've set for the formula and how do I prevent this?
