Excel Excel Macro - Variable in a formula

[dwhite0829]

I'm trying to put a sum formula in an excel macro for data that is never the same length. There will be multiple sum formulas on the same spreadsheet. So my formula when I hardcode the cells in look like this for the first section.

activecell.formulaR1C1 = "=SUM(R[-3]C:R[-1]C)"

But for the next forumla, a few lines down the sheet, I may only have 1 line to sum. So I have a variable that counts the number of rows to sum, I just need to find out how to put that in the formula.

So we'll say my variable,"File_count", is equal to -2
I thought I could make the formula as this "=sum(R[file_count]C:R[-1]C), but I get an "Application-Defined or Object-Defined" error. I hope this make sense. My data would look like below if it worked correctly.

Thanks for any help.


joe 5
joe 10
joe 12
Total 27


nancy 6
Total 6

paul 5
paul 6
Total 11

 

[Stoneboysteve]

You should look at offset.

If you post your macro it may be easier to explain.

Stoneboysteve