CODEWITHSUNDEEP
excelexcel-formulavba
TechGeek
TechGeek

Reputation: 2212

Excel VBA - FormulaR1C1

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.

Upvotes: 4

Views: 10647

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

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

Upvotes: 3

Related Questions