SUM formula with dynamic column range

[trillium]

Hi

I need to put in a SUM formula at the end on some columns of data. The issue is, the range of the columns are dynamic.
Based on other code, I have the locations of these columns, however I don't know how to pass them into the SUM formula. I thought using the .formulaR1C1 method would be easier but not sure.

ActiveCell.Formula = "=sum(R2C[-3]:R2C[-1])"

How do I change the [-3] to be able to pass a value? It could be -5 or -4 for example.
Thanks!
Dava

 

[Joe4]

First off, you need to use ".FormulaR1C1", not ".Formula" if you are going to do that.

Any variables you have just need to be applied outside of the literal text strings and combined together.
See here for an example: https://www.mrexcel.com/forum/excel-questions/487032-how-use-variable-r1c1.html

 

[oldbrewer]

[TABLE="width: 576"]
<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]6[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]30[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]=SUMIF($A$1:$A$10,"<>""")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]if you now put a 10 in A5 the total will be 40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

ActiveCell.Formula = "=sum(R2C[-3]:R2C[-1])"

How do I change the [-3] to be able to pass a value? It could be -5 or -4 for example.

Something like this

Dim firstoffset As Long

firstoffset = -5
ActiveCell.FormulaR1C1 = "=sum(R2C[" & firstoffset & "]:R2C[-1])"

or this

Dim firstoffset As Long

firstoffset = -5
ActiveCell.FormulaR1C1 = Replace("=sum(R2C[#]:R2C[-1])", "#", firstoffset)

 

[trillium]

Thank you!! I like your first example, easier for me to use I think. MUCH APPRECIATED!

 

[trillium]

Thanks, but the columns can change, so A could become G...

 

[trillium]

Yes you're right! I missed the R1C1. I haven't worked with them very often. Thanks for the LINK! This will help me understand R1C1 better I'm sure!

 

[Peter_SSs]

Thank you!! I like ..

Thanks, but the columns can change ...

For the future, when multiple people have responded in the thread it is a good idea to indicate (either by name or by quote snippet like I have here) who you are addressing your comments to.

 

[trillium]

For the future, when multiple people have responded in the thread it is a good idea to indicate (either by name or by quote snippet like I have here) who you are addressing your comments to.

Sorry Peter_SSs - thanks for letting me know!

 

[Peter_SSs]

Sorry Peter_SSs - thanks for letting me know!

That's better.