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

Please Login or Register  to view this content.

but this generates an error

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

[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 !