如何使用 VBA 对选定范围的列求和？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9800808/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to sum columns for a selected range using VBA?
提问by user1204868
After I have selected a range containing numerical values, I want to, via VBA, input a =SUMformula at the bottom of each column, i.e. on the row after the last selected row. For each column it should sum all of the values in the corresponding column of the entire selection.
在我选择了一个包含数值的范围后，我想通过 VBA=SUM在每列的底部输入一个公式，即在最后选择的行之后的行上。对于每一列，它应该对整个选择的相应列中的所有值求和。
How can I do this?
我怎样才能做到这一点？
Right now, I am using the code given by the macro recorder: ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)". The problem is when my range gets bigger than 10 rows it will not take the rows above the bottom 10 into consideration.
现在，我使用的宏录制给予代码：ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"。问题是当我的范围大于 10 行时，它不会考虑底部 10 行以上的行。
采纳答案by Jean-Fran?ois Corbett
This works:
这有效：
Sub MakeSums()
Dim source As Range
Dim iCol As Long
Dim nCol As Long
Dim nRow As Long
Set source = Selection
nCol = source.Columns.Count
nRow = source.Rows.Count
For iCol = 1 To nCol
With source.Columns(iCol).Rows(nRow).Offset(1, 0)
.FormulaR1C1 = "=SUM(R[-" & nRow & "]C:R[-1]C)"
.Font.Bold = True
End With
Next iCol
End Sub
Example:
例子：
回答by Siddharth Rout
Here is a simple non-VBA Approach.
这是一个简单的非 VBA 方法。
Select the cells where you want the sum and press Alt-=.
选择您想要求和的单元格，然后按Alt- =。
SNAPSHOT
快照
And here is a one-line VBA code that does the same thing.
这是一个执行相同操作的单行 VBA 代码。
Sub AutoSum()
'~~> After you select your range
Application.CommandBars.ExecuteMso ("AutoSum")
End Sub
回答by markblandford
You could also do something like this without VBA:
你也可以在没有 VBA 的情况下做这样的事情：
=SUM(OFFSET(INDIRECT(CELL("address")),1-ROW(),0,ROW()-1,1))
This will sum all cells above the cell in which the formula exists.
这将对存在公式的单元格上方的所有单元格求和。
