98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » R1C1 Cell references
R1C1 Cell references
Resolved · Urgent Priority · Version 2003
Wed 14 Jan 2009, 14:28Delegate Anthony said...
Anthony has attended:
Excel VBA Intro Intermediate course
R1C1 Cell references
How do I modify the 1's in the R1C1 formula so that they are varible integers rather than absolute numbers.
Wed 21 Jan 2009, 13:43Trainer Laura said...
RE: R1C1 Cell references
Hi Anthony
Thank you for your question regarding FormulaR1C1 in Excel VBA.
The property of FormulaR1C1 is refering to the format of the formula format you are entering. For example if in cell A4 you want the following formula
=SUM(A1:A3)
You would use the following code in your VBA
Range("A4").FormulaR1C1="=SUM(R[-4]C:R[1]C)"The R1C1 is just part of the name of the property of the cell.
I hope that answers your question.
Laura GB
Fri 23 Jan 2009, 10:57Delegate Anthony said...
RE: R1C1 Cell references
Thank you for your response to my question. However I fear that we are talking at cross purposes. I understand that FormulaR1C1 refers to the a formula format. My question was about the use of that format.
For example with A1 type cell referencing I can write the formula
Range("A1").Value="=sum(B2:B5)"
and with FormulaR1C1 I write
Range("A1").Value="=sum(R[2]C[2]:R[5]C[2])"
However if I had declared a variable counter "Outrow" for example, I could modify my formula in A1 referencing
as follows
Range("A1").Value = WorkSheetFunction.Sum(Cells(Outrow,2),Cells(Outrow+3,2))
This has the effect of modifying the formula depending on the value of Outrow for use in a loop maybe.
My question was how do I introduce the Outrow integer above into the FormulaR1C1?
Not like this I presume
Range("A1").Value="=sum(R[Outrow]C[2]:R[Outrow+3]C[2])"
Kind regards
Fri 23 Jan 2009, 11:10Trainer Laura said...
RE: R1C1 Cell references
Hi Anthony
Thanks for the reply.
To add OutRow into the FormulaR1C1 you need to add it into the string using &. Your answer is half way there. It should be something similar to
Range("A1").Value="=sum(R[" & Outrow & "]C[2]:R[" & Outrow+3 & "]C[2])"Let me know if that works.
Laura GB
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Change the Value of a ConstantWhen using a named constant in a worksheet, you may wish to change the value of that constant. |
