vba 使用VBA为单元格分配公式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27874081/
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
Assign formula to cell using VBA?
提问by Amen Jlili
VBA has been giving me an error 1004for this code:
VBA 一直给我1004这个代码的错误：
Sub UpdateCellsFormula()
Dim FormulaRange As Range
Dim i As Integer
Set FormulaCellsRange = Range("J17", "J95")
i = 17
For Each r In FormulaCellsRange.Cells
r.Formula = Replace("=D17*L21+E17*M21+F17*O21+G17*N21+H17*P21+I17*Q21)/(1+0,85+0,6+0,4+0,37)", "17", i)
i = i + 1
Next
End Sub
Can anyone have a look at it?
任何人都可以看看吗？
回答by GSerg
Formulas assigned with .Formulamust be in En-US locale. The decimal dot in numbers must therefore be ., not ,(1+0.85+0.6+0.4+0.37).
分配的公式.Formula必须在 En-US 语言环境中。因此.，数字中的小数点必须是，而不是,( 1+0.85+0.6+0.4+0.37)。
Also you have a missing opening parenthesis in the beginning, right after =.
此外，您在开头缺少左括号，就在=.
You also might want to learn about absolute references in Excel. That way you can copy the same formula into all cells in FormulaCellsRangewithout replacing anything:
您可能还想了解 Excel 中的绝对引用。这样您就可以将相同的公式复制到所有单元格中，FormulaCellsRange而无需替换任何内容：
Sub UpdateCellsFormula()
ActiveSheet.Range("J17", "J95").Formula = "=(D17*$L+E17*$M+F17*$O+G17*$N+H17*$P+I17*$Q)/(1+0.85+0.6+0.4+0.37)"
End Sub
