将可变范围的单元格求和
我的办公室有一个工作表。有时，一个部分需要一些额外的行，因此我创建了一个按钮，该按钮添加了上述行（对于非excel用户）。本节的底部是一个简单的＆lt; = sum（d82：d92）＆gt;。 这样，只要添加数据，就可以求和。
但是，当添加新行时，总和公式保持不变。我正在尝试将D92更改为D93（等）。 如何添加一条行并更新总和公式，以使其保持动态。 请在以下代码上提供建议：
'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)
编辑： 修复了！
ActiveCell.Offset(1, 3).Select
ActiveCell.FormulaR1C1 = "=SUM(R82C4:R[-1]C)"
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)"
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您不介意使用
offset使用公式，则只需更改= sum（d82：d92）在d93中> = sum（d82：offset（d93，-1,0））。该公式将按预期进行更新：它将一直在自身上方寻找对1行的引用，以找到总和范围的末端。例如，具有range的子（“ d93”）。eulterRow.insert将将公式推入d94，现在为= sum（d82：offset：offset（d94， - - 1,0））。编辑：我建议
offset，以为在这种情况下<代码>索引不可能，但是当然是。可以使用= sum（D82：index（D82：D93，ROW（93:93）-82））输入d93。好处是offset是“波动性函数”，每个工作表更改都会重新计算。使用索引，您没有这个问题。If you don't mind using a formula with
OFFSET, you could just change=SUM(D82:D92)in cellD93into=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 withRange("D93").EntireRow.Insertwill push the formula intoD94, now as=SUM(D82:OFFSET(D94,-1,0)).Edit: I suggested
OFFSET, thinking thatINDEXwasn'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 intoD93. The benefit would be thatOFFSETis a 'volatile function', which will recalculate with each worksheet change. WithINDEX, you don't have this problem.