ExcelBanter - R1C1 Education

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - R1C1 Education (https://www.excelbanter.com/excel-programming/294102-r1c1-education.html)

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

Bernie,

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

Range("B5").FormulaR1C1 = x

"bw" wrote in 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

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

On Sun, 4 Apr 2004 16:29:37 -0700, "Chip Pearson" wrote:
Bernie,

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

Range("B5").FormulaR1C1 = x

"bw" wrote in 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

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

"bw" wrote in message
...
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

On Sun, 4 Apr 2004 16:29:37 -0700, "Chip Pearson"
wrote:
Bernie,

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

Range("B5").FormulaR1C1 = x

"bw" wrote in 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

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

.

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" wrote in 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

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

On Sun, 4 Apr 2004 17:19:19 -0700, "Rick"
wrote:
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

.