vba 用VBA计算excel单元格中的公式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22770905/
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
Calculating formulas in excel cells with VBA?
提问by JC11
I have a series of Excel files all in the same format. The amount of data in each excel file differs. I am making a code that will loop through a folder and perform calculations with all excel files.
我有一系列格式相同的 Excel 文件。每个excel文件中的数据量不同。我正在制作一个代码，它将遍历一个文件夹并使用所有 excel 文件执行计算。
I am using the following code in VBA to determine the number of rows in an excel file:
我在 VBA 中使用以下代码来确定 Excel 文件中的行数：
i = .Cells(.Rows.Count, 2).End(xlUp).Row
I am using the following formula to perform calculations on a range of cells:
我正在使用以下公式对一系列单元格执行计算：
With .Range("D2:D" & i)
.Formula = "=Log(B2)"
End With
How would I calculate the sum of all values in the "D" column in the next available cell in D? This is how the formula would look in theory
我将如何计算 D 中下一个可用单元格中“D”列中所有值的总和？这就是公式在理论上的样子
j = i + 1
With .Range("D" & j)
.Formula = "=Sum(D2:D & i)"
End With
How would I use the D cell with the sum for future calculations? Lets say I wanted E1 "=(D2-$D$ & j)^2"
我将如何将 D 单元格与总和一起用于将来的计算？假设我想要 E1"=(D2-$D$ & j)^2"
Sorry for the vague title, I didn't know how to describe this problem.
抱歉标题模糊，我不知道如何描述这个问题。
回答by Dmitry Pavliv
As follow up from comments, this one works:
作为评论的后续行动，这个有效：
With .Range("D" & j)
.Formula = "=SUM(D2:D" & i & ")"
End With
