vba 使用vba在excel中设置总和公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17852071/
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
set sum formula in excel using vba
提问by sudhakarssd
Working on a excel macros where I am trying to add the cell values from above cells to calculate total value. This is what my data looks like
处理 excel 宏，我试图从上面的单元格中添加单元格值来计算总值。这就是我的数据的样子
Here I want to add above cell values for each column to calculate the sum. To accomplish this i have written a macro as follows.
在这里，我想为每列添加以上单元格值以计算总和。为了做到这一点，我编写了一个宏如下。
For cl = 2 To 5
Worksheets(5).Cells(4, cl).Formula = "=SUM(B4:B6)"
Next cl
This should set the formula to each cell in a row till 5 columns. But it sets the same formula on all cells in a row it should get change according to column. How to set sum formula for each cell for corresponding column ?
这应该将公式设置为一行中的每个单元格，直到 5 列。但是它在一行中的所有单元格上设置了相同的公式，它应该根据列进行更改。如何为相应列的每个单元格设置求和公式？
回答by teylyn
Not sure I quite understand your code. You seem to be writing into row 4, but you also want to sum from row 4 to row 6. That will create a circular reference.
不确定我是否完全理解您的代码。您似乎正在写入第 4 行，但您还想从第 4 行到第 6 行求和。这将创建一个循环引用。
Let's assume the formula is written into row 3 instead. You will want to use the R1C1 reference style to make the cells to sum relative to the current cells.
让我们假设公式改为写入第 3 行。您将需要使用 R1C1 引用样式使单元格相对于当前单元格求和。
A trick to learn what reference to use is:
了解使用什么引用的一个技巧是：
- Get a new worksheet, enter
=SUM(B4:B6)into cell B3 and copy to the right. - Then click File > Options > Formulasand select R1C1 Reference Style.
- Now inspect the formulas in the sheet. You will see this:
=SUM(R[1]C:R[3]C)
- 获取一个新的工作表，输入
=SUM(B4:B6)单元格 B3 并复制到右侧。 - 然后单击文件 > 选项 > 公式并选择R1C1 参考样式。
- 现在检查工作表中的公式。你会看到这个：
=SUM(R[1]C:R[3]C)
This is what you need in the macro.
这就是你在宏中需要的。
For cl = 2 To 5
Worksheets(5).Cells(3, cl).FormulaR1C1 = "=SUM(R[1]C:R[3]C)"
Next cl
回答by Serafin William
For your case, you can use this:
对于你的情况，你可以使用这个：
For cl = 2 To 5
ColName = Left(Cells(1, cl).Address(0, 0), (Cells(1, cl).Column < 27) + 2)
ValueSum = "=SUM(" & ColName & "4:" & ColName & "6)"
Worksheets(5).Cells(4, cl).Formula = ValueSum
Next
回答by wallyeye
I think you can just reference the whole formula range and Excel will be smart enough to adjust the columns.
我认为您可以只引用整个公式范围，Excel 将足够智能以调整列。
With no loop:
没有循环：
Worksheets(5).Cells(4, cl).resize(1,4).Formula = "=SUM(B4:B6)"
Worksheets(5).Cells(4, cl).resize(1,4).Formula = "=SUM(B4:B6)"
回答by user1685490
Try something like this.
尝试这样的事情。
For cl = 2 To 5
ColName = Left(Cells(1, cl).Address(False, False), 1 - (cl > 26))
Worksheets(5).Cells(4, cl).Formula = "=SUM(" & ColName & "4:" & ColName & "6)"
Next cl
回答by Stefan
some more details to R1C1 and R[1]C[1] style in formulas. As far as I know R[1]C[1] creates a relative reference and R1C1 creates a absolute reference. But keep in mind that the figures for R[x] and C[y] are an offset to the cell which contains the formula.
公式中 R1C1 和 R[1]C[1] 样式的更多细节。据我所知 R[1]C[1] 创建一个相对引用，R1C1 创建一个绝对引用。但请记住，R[x] 和 C[y] 的数字是包含公式的单元格的偏移量。
That means if you want to show the sum of A1:B4 in C5 the code has to be like this:
这意味着如果你想在 C5 中显示 A1:B4 的总和，代码必须是这样的：
Worksheets(5).Cells(5, 3).FormulaR1C1 = "=SUM(R[-4]C[-2]:R[-1]C[-1])"
If you want to the same but ending up with an absolute reference is aht to look like this.
如果您想要相同但以绝对引用结束，则看起来像这样。
Worksheets(5).Cells(5, 3).FormulaR1C1 = "=SUM(R1C1:R4C2)"
回答by Mariusz Krukar
You can use very simple formula like below:
您可以使用非常简单的公式，如下所示：
Sub sum_month()
Sheets("13").Activate
Range("D2").Formula = "=SUM(A1+A2+A3)"
End Sub
And next just hold and drag the cell to fill up another rows automatically.
接下来只需按住并拖动单元格即可自动填充另一行。
