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

[Herls08]

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

 

[farmerscott]

Welcome to the forum......

what do you get when you use-

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

 

[Herls08]

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

 

[farmerscott]

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

 

[Herls08]

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]

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.

Glad i could help.

cheers

FarmerScott

 

[Jonmo1]

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]

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]

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
We'd need some more information to provide a more 'elegant' solution
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.

 

[Herls08]

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>