CODEWITHSUNDEEP
excelvbasumrange
Doc_B
Doc_B

Reputation: 3

Summing cells with a variable range

I have a worksheet used for my office. Occasionally, a few extra lines are needed in one section, so I created a button that adds said line (for the non-excel users). At the bottom of the section is a simple <=SUM(D82:D92)>. In this way, whenever data is added, the amount is summed.

However, when a new line is added, the sum formula stays the same. I'm trying to change the D92 to D93 (and etc.). How can I add a line AND update the sum formula so that it stays dynamic. Please give advice on the below code:

'botton cell in sum range
Dim BC As String
Dim rng As Range
BC = ActiveCell.Address
Set rng = Range(Range("d82"), Range(BC))
ActiveCell.Offset(1, 0).Select
ActiveCell = WorksheetFunction.Sum(rng)

EDIT: Fixed it!

ActiveCell.Offset(1, 3).Select
ActiveCell.FormulaR1C1 = "=SUM(R82C4:R[-1]C)"

Upvotes: 0

Views: 846

Answers (1)

ouroboros1
ouroboros1

Reputation: 14064

If you don't mind using a formula with OFFSET, you could just change =SUM(D82:D92) in cell D93 into =SUM(D82:OFFSET(D93,-1,0)). This formula will update as expected: it will just keep looking for a reference to 1 row above itself to find the end of the sum range. E.g. a sub with Range("D93").EntireRow.Insert will push the formula into D94, now as =SUM(D82:OFFSET(D94,-1,0)).

Edit: I suggested OFFSET, thinking that INDEX wasn't possible in this case, but of course it is. Same result can be gained by using =SUM(D82:INDEX(D82:D93,ROW(93:93)-82)) entered into D93. The benefit would be that OFFSET is a 'volatile function', which will recalculate with each worksheet change. With INDEX, you don't have this problem.

Upvotes: 1

Related Questions