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.

 

[RockandGrohl]

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

Yeah I tested on a new sheet too, worked beautifully. I wonder what's going on here then.

 

[RockandGrohl]

OH MY GOD I MISPELLED INDIRECT COST RAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA


Thanks Rory.

 

[RoryA]

at least now we know!

 

[RockandGrohl]

at least now we know!

To be fair Indirect and Indrect are basically the same word, come on Excel, do better....

:'D