VBA Absolute reference based on A1

[bobaol]

Hello, I need to use the value in A1 as my absolute reference. here is the formula in the cell =INDEX($A$36907:$BBW$999999,MATCH(TRIM(AH2),$A$36907:$A$999999,0),1) when i record it, the formula becomes this:
Selection.FormulaR1C1 = "=INDEX(R36907C1:R999999C1427,MATCH(TRIM(RC[-6]),R36907C1:R999999C1,0),1)" I have a value in cell A1 that changes depending on the day. I tried changing it to this, Selection.FormulaR1C1 = "=INDEX(R&range("a1")&C1:R999999C1427,MATCH(TRIM(RC[-6]),R&range("a1")&C1:R999999C1,0),1)" but it does not work. Any help is appreciated. thanks in advance.

 

[dermie_72]

You can remove the Selection.FormulaR1C1 and replace it with Selection.Formula, then replace the macro formula with the excel one.

Selection.Formula = "=INDEX($A$36907:$BBW$999999,MATCH(TRIM(AH2),$A$36907:$A$999999,0),1)"

This has to be done manually after you record a macro.

 

[kevin9999]

Alternatively, where you have (for example)

R&range("a1")&C

replace with

R" & [a1] & "C

Note the quotation marks.