Bookmark page
Printable version
Course updatesForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » R1C1 Cell references
|
|
|
Bookmark this page|
The UK's most regular 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. |
R1C1 Cell references
Resolved · Urgent Priority · Version 2003
Reply
R1C1 Cell references
How do I modify the 1's in the R1C1 formula so that they are varible integers rather than absolute numbers.For upcoming training course dates see: Pricing & availability
RE: R1C1 Cell references
Hi AnthonyThank 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
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
RE: R1C1 Cell references
Hi AnthonyThanks 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
RE: R1C1 Cell references
Thanks that works perfectly.Kind regards
|
 |
|
» Forum post: Duplicate items |
How do find duplicate data in excel spreadsheet
