Excel VBA - FormulaR1C1

Question

I follow the formula using VBA code,

.Range("M2:M" & LastRow).FormulaR1C1 = "=vlookup(RC2 & RC3 & RC6 & RC7,trip_length!A:F,6,False)" 

But when I see the formula by pressing F2, it is displayed as shown below:

 =VLOOKUP($B2 & $C2 & $F2 & $G2,trip_length!A:(F),6,FALSE) 

All is correct. EXCLUDE brackets around F. And because of this, the formula returns an error.

I tried a lot, but could not figure out how to use a static range (of another sheet) with FormulaR1C1.

Please, help. Thanks.

Answer 1

When using .FormulaR1C1 all range references must be in R1C1 format. Therefore use:

 .Range("M2:M" & LastRow).FormulaR1C1 = _ "=vlookup(RC2 & RC3 & RC6 & RC7,trip_length!C1:C6,6,False)"