CODEWITHSUNDEEP
excelvba
Bhinang Tejani
Bhinang Tejani

Reputation: 11

Using variable in relative range instead of absolute value

This code is working fine:

ActiveCell.FormulaR1C1 = "=Sum(R[-6]C:R[-1]C)"

But instead of -6 in row I want to put a defined variable - lets say I define x and then I write it as follows:

ActiveCell.FormulaR1C1 = "=Sum(R[-x]C:R[-1]C)"

But that gives me an error. How can I do that?

Upvotes: 1

Views: 29

Answers (1)

Shai Rado
Shai Rado

Reputation: 33692

You need to separate the constant Text part of your formula with the Variable, you do that by adding " before and after followed by the &. Like: " & x & "

Try the code below:

Dim x As Long

x = 6
ActiveCell.FormulaR1C1 = "=Sum(R[-" & x & "]C:R[-1]C)"

Note: Try avoid using ActiveCell, instead use fully qualified Cells and Range. For instance: 

Worksheets("Sheet1").Range("D10").FormulaR1C1 = "=Sum(R[-" & x & "]C:R[-1]C)"

Upvotes: 1

Related Questions