Excel VBA - FormulaR1C1

4
votes

I am following 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 shows as below:

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

It's all correct EXCEPT the brackets around F. And because of that, formula returns an Error.

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

Please help. Thanks.

1
excelexcel-formulavba

1 Answers

3
votes

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)"