vba 将公式设置为一系列单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15522538/
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 formula to a range of cells
提问by Vpp Man
this is simple demo of what i want to do. I want to set a formula to a range of cells(eg. C1 to C10).
这是我想要做的简单演示。我想将公式设置为一系列单元格（例如 C1 到 C10）。
Range("C1").Formula = "=A1+B1"
but how to make formula use dynamic cells like this:
但是如何使公式使用这样的动态单元格：
Range("C1:C10").Formula = "=Ax+Bx"
so in reality it is like,
所以实际上它就像，
C1 = A1 + B1
C2 = A2 + B2
C3 = A3 + B3
C4 = A4 + B4
C5 = A5 + B5
...
C10 = A10 + B10
how to change RHS of this formula to make above working: Range("C1:C10").Formula = "=Ax+Bx"
如何更改此公式的 RHS 以使上述工作正常： Range("C1:C10").Formula = "=Ax+Bx"
采纳答案by The King
I would update the formula in C1. Then copy the formula from C1 and paste it till C10...
我会更新 C1 中的公式。然后从 C1 复制公式并将其粘贴到 C10 ...
Not sure about a more elegant solution
不确定更优雅的解决方案
Range("C1").Formula = "=A1+B1"
Range("C1").Copy
Range("C1:C10").Pastespecial(XlPasteall)
回答by neilxdims
Range("C1:C10").Formula = "=A1+B1"
Simple as that.
就那么简单。
It autofills (FillDown) the range with the formula.
它使用公式自动填充 (FillDown) 范围。
回答by Baumann
I think this is the simplest answer possible: 2 lines and very comprehensible. It emulates the functionality of dragging a formula written in a cell across a range of cells.
我认为这是最简单的答案：2 行并且非常易于理解。它模拟将单元格中写入的公式拖动到一系列单元格的功能。
Range("C1").Formula = "=A1+B1"
Range("C1:C10").FillDown
回答by user2626806
Use FormulaR1C1:
使用公式R1C1：
Cells((1,3),(10,3)).FormulaR1C1 = "=RC[-2]+RC[-1]"
Unlike Formula, FormulaR1C1 has relative referencing.
与公式不同，FormulaR1C1 具有相对引用。
回答by Suyog Patil
Use this
用这个
Sub calc()
Range("C1:C10").FormulaR1C1 = "=(R10C1+R10C2)"
End Sub
回答by Kelly Kochanski
If you're trying to fill a column, you can do this on Excel 2010 with a single keyboard command:
如果您尝试填充一列，您可以在 Excel 2010 上使用单个键盘命令执行此操作：
- Set the formula of the upper-left-most cell
- Select the entire range
- Hit Ctrl-d
- 设置左上角单元格的公式
- 选择整个范围
- 按 Ctrl-d
Ctrl-D will either fill-across a selected row, or fill-down a selected array or column. Relative referencing is applied.
Ctrl-D 将填充选定的行，或填充选定的数组或列。应用相对引用。
