VBA - Summing a dynamic gap range between two named ranges?

[RockandGrohl]

Hi all, can't even figure out how to phrase this in Google or ChatGPT to get something that is nice and clean.

I have a sheet where in column J, there are costs, and sub-totals for those costs.

Each subtotal is a named range, for instance:
"DirectCostSum" is J23
"IndirectCostSum" is J31

What I'd like to do is for IndirectCostSum cell value to be a sum of J24:J30.

These subtotals are dynamically placed based on how many products I am summing, so the gap between the named ranges I want to sum is always changing.

What I'm after is a nice, one line chunk of code which will be like..

range("IndirectCostSum").formular1c1 = "=SUM( [DirectCostSum + 1],[IndirectCostSum -1]),

That's the best way I can describe it lol. Hopefully that makes sense.

 

[RoryA]

Does the actual formula itself need to be dynamic, or do you just need to calculate the right range at runtime of the code?

 

[RockandGrohl]

Does the actual formula itself need to be dynamic, or do you just need to calculate the right range at runtime of the code?

Just at runtime, I'm intending to replace the formula with values at the end of the process. It just needs to calculate the nice range.

I can of course do it a sloppy way by just getting the address of each named range, saving them as their own variable and then doing a cells formula instead but this is many many lines and I reckon there's away to do it cleanly.

Chat GPT gives me this which looks like it might work but I get the 1004 error.

Range("IndirectCostSum").Formula = "=SUM(" & Range("DirectCostSum").Offset(1, 0).Address & ":" & Range("IndirectCostSum").Offset(-1, 0).Address & ")"

 

[RoryA]

That should work. Or you could use:

Range("IndirectCostSum").FormulaR1C1 = "=SUM(" & Range("DirectCostSum").Offset(1, 0).Address(referencestyle=xlr1c1) & ":R[-1]C)"

 

[RockandGrohl]

That should work. Or you could use:

Range("IndirectCostSum").FormulaR1C1 = "=SUM(" & Range("DirectCostSum").Offset(1, 0).Address(referencestyle=xlr1c1) & ":R[-1]C)"

Unfortunately that still gives Application or Object defined error (1004)

This is usually where the named ranges aren't correct but I've verified they're spot on.

FYI This is still running inside the Worksheet_Activate module so I added the references as such:

ios.Range("IndirectCostSum").FormulaR1C1 = "=SUM(" & ios.Range("DirectCostSum").Offset(1, 0).Address(ReferenceStyle = xlR1C1) & ":R[-1]C)"

 

[RoryA]

Are those ranges direct cell references?

Just noticed I missed a colon when typing the code earlier:

ios.Range("IndirectCostSum").FormulaR1C1 = "=SUM(" & ios.Range("DirectCostSum").Offset(1, 0).Address(ReferenceStyle:=xlR1C1) & ":R[-1]C)"

 

[RockandGrohl]

Are those ranges direct cell references?

Errr... I think so?

"DirectCostSum" is a named range for J23, Indirect for J31

 

[RoryA]

I just updated my last post.

 

[RockandGrohl]

I just updated my last post.

Nice catch, but still the same 1004 error.

 

[RoryA]

It's not the code - I just tested it and it works fine.