Wed 3 Dec 2008, 10:28Delegate Andrew said...
Andrew has attended:
Excel VBA Intro Intermediate course
FormulaR1C1 notation help
Hi all,
I was wondering how in VBA where we have the FormulaR1C1 notation as:
activecell.FormulaR1C1 = "=SUM(RC[+1]:RC[+3])"
If how many rows/columns back I want to go is variable, how can I set the +1 and +3 values to some variable integer?
Wed 21 Jan 2009, 13:49
Edited on Wed 21 Jan 2009, 13:50Trainer Laura said...
RE: FormulaR1C1 notation help
Hi Andrew
Thank you for your question regarding using FormuaR1C1.
The FormulaR1C1 property is a string so to use variables for the column offset in the formula you would need to add the different pieces of the string together using &.
Using your example and two integer variables iColOffset1 and iColOffset2 the code would change to
ActiveCell.FormulaR1C1 = "=SUM(RC[+" & iColOffset1 & "]:RC[+" & iColOffset2 & "])"
I hope that helps.
Please let me know how you get on.
Laura GB
