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.
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
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 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
