Relative Cell Reference in VBA Not Working

[ttbuson]

For some reason, the below snippet of code inserts a formula where the lookup value for the match functions actually refers to cell RC1, rather than the first column of the sheet and the row of the active cell. Does anyone know why this code would do this? I think it has something to do with the named arrays in the functions, but I'm not sure. Thanks in advance!

With Sheets("Control").Cells(LastRow1 * 2 - 2, 2)
    .Formula = _
    "=INDEX(Exposures,MATCH(RC1,ME_Dates,0),COLUMN())*INDEX(Weights,MATCH(RC1,MW_Dates,0),COLUMN())"
    .NumberFormat = "0.0%"
    .HorizontalAlignment = xlRight
End With

 

[pplstuff]

try:


"=INDEX(Exposures,MATCH(" & RC1 & ",ME_Dates,0),COLUMN())...

or create a string with the relative cell reference:
s = somecell.address(0,0)
"=INDEX(Exposures,MATCH(" & s & ",ME_Dates,0),COLUMN())...


Sidenote:
I'm not as familiar with RxCx, so I'm not 100% on that one. The string version will work as this is what I use most of the time.

 

[ttbuson]

Thank you! The second suggestion worked. Is there a way to fix the column reference with this? So, if I wanted the reference to be $A45 instead of A45, could I do that with your method?

 

[pplstuff]

In this case, you could use:
s = somecell.address(0,0)
"=INDEX(Exposures,MATCH($" & s & ",ME_Dates,0),COLUMN())...


I'm sure there's a way to change "s = somecell.address(0,0)" to something else... but I'm not sure off the top of my head.