Excel VBA 修改现有方程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17625823/
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
Excel VBA Modify Existing Equation
提问by Mark Austin
I am trying to figure out how to modify an existing equation in VBA. The current equation is sum(A1+A3+A5) I am skipping every other row. I have a macro to add two rows at the end of the column A so when those new rows are added i need to update that equation to sum(A1+A3+A5+A7).
我想弄清楚如何修改 VBA 中的现有方程。当前方程是 sum(A1+A3+A5) 我每隔一行跳过一次。我有一个宏可以在 A 列的末尾添加两行，因此当添加这些新行时，我需要将该等式更新为 sum(A1+A3+A5+A7)。
回答by chris neilsen
A better approach may be to use a different formula.
更好的方法可能是使用不同的公式。
Eg, to sum all Odd rows in column A, use
例如，要对 column 中的所有奇数行求和A，请使用
=SUMPRODUCT(A:A*ISODD(ROW(A:A)))
回答by Andy G
if the formula is changed to =A1+A3+A5then code could be:
如果公式更改为=A1+A3+A5那么代码可能是：
Range("B1").Formula = Range("B1").Formula & "+" & Range("A7").Address
or, without dollar signs,
或者，没有美元符号，
Range("B1").Formula = Range("B1").Formula & "+" & Range("A7").Address(False, False)
Addedin response to OPs comment. If I assume the new cell is two rows above where the formula is, which is the currently the active cell:
添加以响应 OP 评论。如果我假设新单元格在公式所在的上方两行，这是当前活动的单元格：
ActiveCell.Formula = Replace(ActiveCell.Formula, ")", "," & _
ActiveCell.Offset(-2, 0).Address(False, False) & ")")
Adjust the value -2 as necessary.
根据需要调整值 -2。
