Using Variables in an R1C1 sum formula

[miwoodar]

Greetings -

I am writing a report generator and am having difficultites adding a sum formula to the bottom of a range.

I have a range of data between H2:X23 (this will change each time the report is run). I can select the range where I need to drop in my formulas via variables...I just can't seem to figure out how to write the summing formulas that use the variables. This is where I am...

Range(Cells(RowNum + 1, 8), Cells(RowNum + 1, Columnsinsheet)).Select
Selection.FormulaR1C1 = "=SUM(R[-1]C:R[-1]C)"

I'm stuck on the R1C1 formula. Each column needs to sum from row 2 to RowNum. Can anyone please help?

>M

____________________
Mike

 

[SkipVought]

Hi,

May I make a suggestion. Aggregations at the BOTTOM, are a vestage of the paper, pencil & adding machine age, when accountants had to subtotal each page and cross check each sum.

But this is the twenty first century! With spreadsheets, you don't need page subtotals. You might need subtotals based on some report division, but think about it: why makd the user HUNT for the most imortant piece of information, when you can put it right at the top.

Putting aggregations at the TOP of a report, does several things.

1. The totals are the first thing that you see.
2. The totals can be displayed on every page. (how many times have you had to flip back and forth to check on some total at the BOTTOM, on page 72?)
3. In some cases you may be able to display subtotals at the top as well, giving a nice SUMMARY of the report.

I almost NEVER put totals at the bottom. Even If I make a PivotTable report, I insert a row at the top os the sheet to display the totals that are at the bottom. On very wide reports, I often put ROW TOTALS to the left of the report table. Then using File/Page Setup - Sheet Tab, I set the ROWS & COLUMNS to display not only the column & row headings, but also the TOTALS.

Skip,

_[red][/red]

 

[PHV]

And whet about this ?
Selection.FormulaR1C1 = "=SUM(R2C:R[-1]C)"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[miwoodar]

Thanks Skip/PHV.

But...is it possible to use variables in my RCR1 formulas? What kind of puncuation do I use to let VBA know that I am trying to use a variable from the code? It would give me a lot more flexibility in my reporting.

____________________
Mike

 

[CautionMP]

miwoodar,
Assumes the RowNum is 23 and the sum is current column rows 2 to 23.

Cells(RowNum + 1, Columnsinsheet)).FormulaR1C1 = "=SUM(R[" & 2 - RowNum & "]C:R[" & RowNum & "]C)"

You just need to add your RC offset value to the string you pass in the R1C1 string.

Hope this helps,
CMP


[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT+08:00) Singapore

 

[miwoodar]

CMP - thanks so much. My life just got a lot easier.

____________________
Mike