R1C1 Education

[bw]

Sub R1C1Test()
Range("C5").Select
x = "=SUM(R[" & -4 & "]C:R[" & -1 & "]C)"
Selection.FormulaR1C1 = x
End Sub

This puts the formula "=SUM(C1:C4)" in Cell C5.

How do I modify this to put the same formula in Cell B5? A little education please...

Thanks,
Bernie

 

[Chip Pearson]

Bernie,

Perhaps I'm missing something, but just use something like

Range("B5").FormulaR1C1 = x

 

[bw]

Chip,

Your suggestion makes the formula in B5 ""=SUM(B1:B4)". I want the formula in B5 to
be "=SUM(C1:C4)".

I guess I mistated the problem on my first question...

Bernie

Bernie,

Perhaps I'm missing something, but just use something like

Range("B5").FormulaR1C1 = x

Sub R1C1Test()
Range("C5").Select
x = "=SUM(R[" & -4 & "]C:R[" & -1 & "]C)"
Selection.FormulaR1C1 = x
End Sub

This puts the formula "=SUM(C1:C4)" in Cell C5.

How do I modify this to put the same formula in Cell B5? A little education please...

Thanks,
Bernie

 

[Chip Pearson]

Bernie,

Try the following:

X = "=SUM(R[" & -4 & "]C[1]:R[" & -1 & "]C[1])"
Range("B5").FormulaR1C1 = X


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Chip,

Your suggestion makes the formula in B5 ""=SUM(B1:B4)". I want the formula in B5 to
be "=SUM(C1:C4)".

I guess I mistated the problem on my first question...

Bernie

Bernie,

Perhaps I'm missing something, but just use something like

Range("B5").FormulaR1C1 = x

Sub R1C1Test()
Range("C5").Select
x = "=SUM(R[" & -4 & "]C:R[" & -1 & "]C)"
Selection.FormulaR1C1 = x
End Sub

This puts the formula "=SUM(C1:C4)" in Cell C5.

How do I modify this to put the same formula in Cell B5? A little education please...

Thanks,
Bernie

 

[Rick]

You can modify this, and still have the same formula in
Cell B5 the following way:

Sub TestR1C1()
Dim strSUM As String
strSUM = "=SUM(C1:C4)"
ActiveSheet.Cells(5, 2).Formula = strSUM
End Sub

Or this way:

Sub TestR1C1Rev1()
Dim strSUM As String
strSUM = "=SUM(C" & 1 & ":C" & 4 & ")"
ActiveSheet.Cells(5, 2).Formula = strSUM
End Sub

Or this way:

Sub TestR1C1Rev2()
Dim strSUM As String
strSUM = "=SUM(R[" & -4 & "]C[1]:R[" & -1 & "]C[1])"
ActiveSheet.Cells(5, 2).FormulaR1C1 = strSUM
End Sub

Or this way: (which is the answer I think you want)

Sub TestR1C1Rev3()
Dim x As String
x = "=SUM(R[" & -4 & "]C[1]:R[" & -1 & "]C[1])"
Range("B5").Select
Selection.FormulaR1C1 = x
End Sub

Without the brackets for C[1] you will find some
differences. Take the brackets out, and you will see.
There's lots more that you can do with these....

Rick

 

[Patrick Molloy]

you can reference rows and columns directly or indirectly using FormulaR1C1
example:
"=R1C1" is absolute and is cell A1
"=RC1" is the same nor as the cell with the formula, and in column 1.
to use an offset , place the number of rows or columns within square
brackets.
"=RC[5]" refers to a cell in the same row , but 5 columns to the right.

hth

 

[bw]

Rick,

Thanks for the education. Your last example (the answer you thought I wanted) was in
fact the one I was looking for, but as you can see from my question, I didn't understand
the use of R1C1 at all, so all of your examples are appreciated.

Thanks again,
Bernie

You can modify this, and still have the same formula in
Cell B5 the following way:

Sub TestR1C1()
Dim strSUM As String
strSUM = "=SUM(C1:C4)"
ActiveSheet.Cells(5, 2).Formula = strSUM
End Sub

Or this way:

Sub TestR1C1Rev1()
Dim strSUM As String
strSUM = "=SUM(C" & 1 & ":C" & 4 & ")"
ActiveSheet.Cells(5, 2).Formula = strSUM
End Sub

Or this way:

Sub TestR1C1Rev2()
Dim strSUM As String
strSUM = "=SUM(R[" & -4 & "]C[1]:R[" & -1 & "]C[1])"
ActiveSheet.Cells(5, 2).FormulaR1C1 = strSUM
End Sub

Or this way: (which is the answer I think you want)

Sub TestR1C1Rev3()
Dim x As String
x = "=SUM(R[" & -4 & "]C[1]:R[" & -1 & "]C[1])"
Range("B5").Select
Selection.FormulaR1C1 = x
End Sub

Without the brackets for C[1] you will find some
differences. Take the brackets out, and you will see.
There's lots more that you can do with these....

Rick

-----Original Message-----
Sub R1C1Test()
Range("C5").Select
x = "=SUM(R[" & -4 & "]C:R[" & -1 & "]C)"
Selection.FormulaR1C1 = x
End Sub

This puts the formula "=SUM(C1:C4)" in Cell C5.

How do I modify this to put the same formula in Cell B5? A little education please...

Thanks,
Bernie


.