# Input the "value" (not the formula) of a R1C1 notation formula?

#### Herls08

##### New Member
I’m summing a list of sales by customer code , policy count, sales amount in \$ & commission amount in \$. The numbers of sales vary by salesperson so I need to sum a varying sized range. I thought that the R1C1 notation method would be best. However, I can only input the formula in the cell and I prefer inputting just the value of the range (not the formula).

[more code goes here…]
c = count of sales
‘Insert Total & Sum
PrtRng.Offset(c + 1, 0) = "Totals"
PrtRng.Offset(c + 1, 1).Formula = Application.Sum(Range(Cells(x + 1, 2), Cells(c + 1, 2))) 'this works, but not R1C1 notation

PrtRng.Offset(c + 1, 1).FormulaR1C1 = "=Sum(R[-6]c:R[-1]C)" 'inputs the Formula in cell, Not the value

'PrtRng.Offset(c + 1, 1).FormulaR1C1 = Sum(R[-6]c:R[-1],C) '= Compile error: Expected: list separator or )

My question is can I input the value of the sum using R1C1 notation? If so, how? Thanks for your help

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### farmerscott

##### Well-known Member
Welcome to the forum......

what do you get when you use-

Code:
``[FONT=Arial][COLOR=#000000]PrtRng.Offset(c + 1, 1).FormulaR1C1 = WorksheetFunction.Sum(R[-6]C:R[-1],C)[/COLOR][/FONT]``

#### Herls08

##### New Member
I get the same error. I've also tried = Application.Worksheetfunction.sum(R[-6]C:R[-1],C). Same compile error

#### farmerscott

##### Well-known Member
In the 2007 Excel help file it states RE FormulaR1C1 style-"If the cell contains a constant, this property returns the constant. If the cell is empty, the property returns an empty string. If the cell contains a formula, the property returns the formula as a string, in the same format in which it would be displayed in the formula bar (including the equal sign)."So it looks like you can not get VBA to evaluate the formula and put it in the cell/range as a value using the R1C1 style. Is this something you are doing out of interest... the best coders on this forum do it your first way.hope that helps,FarmerScott

Last edited:

#### Herls08

##### New Member
FarmerScott, Thanks a lot for helping me and for providing the reference to the help file. I have looked-up the reference you gave me in the help file and will use the help file more now. I'm also glad to see that the best coders use my first method. I am trying write efficient code and for some reason the R1C1 method seemed like the right choice for this problem. Thanks again. Herls08<o></o>

#### farmerscott

##### Well-known Member
Herls08,

The 'help' in Excel i find is both good and bad. Often it can be highly summarised and technical, other times it 'hits the nail on the head'. It is not a bad way to start on a problem.

Referencing ranges via the A1 notation is easier and you will find that most of the good coders here, create a lot of variables so if the size of the data changes then you are not going back thru your code and making ajustment everywhere. For some of us, using the R1C1 style probably comes from recording our first macros (and trying to understand them).

There is some very smart and skilled people on this forum. Some of the coders I like are MichaelM, Hiker95, Jonmo1, Rick Rothstein, Mireabu and JoeMo. VBA seems to have some great flexibility in the code so it can be an interesting thread when the good coders get a little competitive to see who can write the fastest or shortest code.

cheers

FarmerScott

#### Jonmo1

##### MrExcel MVP
If this actually puts the correct formula in the cell
PrtRng.Offset(c + 1, 1).FormulaR1C1 = "=Sum(R[-6]c:R[-1]C)"
Then you've already done the heavt lifting.

It's probably easiest to go ahead and do that, then convert it to it's value.

Put the formula in the cell
Then convert it to it's value...

PrtRng.Offset(c + 1, 1).FormulaR1C1 = "=Sum(R[-6]c:R[-1]C)"
PrtRng.Offset(c + 1, 1).Value = PrtRng.Offset(c + 1, 1).Value

#### farmerscott

##### Well-known Member
Hi Jonmo1,

1. would you expect that coding to run marginally slower than other options, but insignificantly on less than 1000 rows?
2. could the 2nd line be summaried to .value=.value??

thx

FarmerScott

#### Jonmo1

##### MrExcel MVP
1. Possibly, but very marginally (that's why I said 'easiest', not necessarily the best)
It Depends on what the other options are, and as you pointed out how large the dataset is.
I presume there's some kind of loop involved
Even then, I might still opt for putting in the formula then converting to the value.
Because you can put the formula in ALL cells in one stroke, rather than looping through the cells.

2. Yes, using a With statement
But I'm not sure if that's actually faster, just maybe easier to write if you're doing several things with the same cell.

Last edited:

#### Herls08

##### New Member
Thanks Jonmo1 & FarmerScott. The data source I'm summing has 15,000 rows (sales) and 18 columns. The VBA code I have works surprisingly fast. I knew there was another way to input the value, I just thought I was doing something wrong with the R1C1 method. After I summarize the data I need to format the report so I can convert the formula's to values during the formatting sequence, as well. Thanks again.<o></o>

Replies
2
Views
80
Replies
6
Views
204
Replies
2
Views
588
Replies
9
Views
1K
Replies
2
Views
136

1,195,858
Messages
6,011,979
Members
441,661
Latest member
Pammie007

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back