Using variable in FormulaR1C1

[amphinomos]

Hi,

I'm getting a "Run-time error 1004 : Application-defined or object-defined error" when trying to use variables in a R1C1 formula.
This works fine

Sheets(1).Cells(3, 1).FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"

but this generates an error

Dim i as integer
Dim j as integer

i = -2
j = -1

Sheets(1).Cells(3, 1).FormulaR1C1 = "=SUM(R[i]C:R[j]C)"

Is it not possible to use variables here ? If so, are there any workarounds ? I would like VBA to generate an actual formula in the cell and not the result.

Thanks,
amphi

[Norie]

Try this.

Dim i as integer
Dim j as integer

i = -2
j = -1

Sheets(1).Cells(3, 1).FormulaR1C1 = "=SUM(R[" & i & "]C:R[" & j & "]C)"

[MrShorty]

You need to use the concatenate operator (&) to combine the text literals and the variables into the final formula text string:
"sum(r[" & i & "]c:r[" & j & "]c)". You can see that there are 5 individual text strings that concatenate together to form the final text string assigned to the formular1c1 property.

[amphinomos]

Don't understand why this works but thanks Norie !

Edit: now I do. Thanks MrShorty !