VBA SUM with variable amount of cells

[Strooprover]

Hi all,
I want to put a sum formula in a cell but I want my formula to depend on the value of another cell. So for instance:

A regular sum formula with relative reference looks like this:
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"

However, I want to change the -2 value that I colored green into another variable.
For Instance.
For i = 1 to 50
Range("F" & i).Value

SUMVAL = Range("F" & i).Value
next i

And then I want to use the value of Range("F"&i) to decide how much offset the sum formula needs to use.

So = ActiveCell.FormulaR1C1 = "=SUM(R[sumval]C:R[-1]C)"

How can I accomplish this?

Thanks in advance!

 

[Jonmo1]

Need to use concatenate...

Try

ActiveCell.FormulaR1C1 = "=SUM(R[" & sumval & "]C:R[-1]C)"