excel 2003 vba：将公式写入单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16884238/
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 2003 vba: write formula into cell
提问by Jonas Herman
I tried to write a formula into a range and get the following message: Run-time error '1004': Application-defined or object-defined error. I use the following code but I don't understand why this is not working:
我尝试将公式写入一个范围并收到以下消息：运行时错误“1004”：应用程序定义或对象定义错误。我使用以下代码，但我不明白为什么这不起作用：
LastRow = Sheets("Source").Cells(Rows.Count, "B").End(xlUp).Row
For Each cell In Sheets("Target").Range("I2:I" & CStr(LastRow)).Cells
cell.Formula = "=IF(D2=E2;""OLD"";""NEW"")"
Next
回答by Peter Albert
As already indicated in the comments, you're facing an issue with the regional settings. The Range.Formulaproperty is using the English default, i.e. you need to replace the German separator ;with ,. This way it is ensured that your code will run on any language version.
正如评论中已经指出的那样，您遇到了区域设置问题。该Range.Formula属性使用默认的英语，即你需要更换德国分离;用,。这样可以确保您的代码可以在任何语言版本上运行。
For completeness: You can also use the Range.FormulaLocalproperty, where you can provide the German formula. However, I would strongly recommend to use this property read-only (if at all). If you write a formula with this property, you're code is guaranteed to break on any non-German system!
为了完整性：您还可以使用Range.FormulaLocal属性，您可以在其中提供德国公式。但是，我强烈建议将此属性设为只读（如果有的话）。如果你用这个属性写一个公式，你的代码肯定会在任何非德语系统上崩溃！
Two more comments on your code though:
不过，对您的代码还有两条评论：
At the moment, you're placing the same formula in each cell without adjusting the row number, i.e. each row will have the same result based on the input in row 2. You could either build each formula, replacing the 2 with a counter. Or much easier, use the RC1 notation:
cell.FormulaR1C1 = "=IF(RC[-5]=RC[-4],""OLD"",""NEW"")"There is actually no need to loop and allocate the formula to each cell individually. Instead, simply replace your For loop with this line:
Sheets("Target").Range("I2").Resize(LastRow-1).Formula= _ "=IF(D2=E2,""OLD"",""NEW"")"
目前，您在每个单元格中放置相同的公式而不调整行号，即根据第 2 行中的输入，每行将具有相同的结果。您可以构建每个公式，用计数器替换 2。或者更简单，使用 RC1 表示法：
cell.FormulaR1C1 = "=IF(RC[-5]=RC[-4],""OLD"",""NEW"")"实际上不需要循环并将公式单独分配给每个单元格。相反，只需用以下行替换您的 For 循环：
Sheets("Target").Range("I2").Resize(LastRow-1).Formula= _ "=IF(D2=E2,""OLD"",""NEW"")"
This way, you don't even need to bother about the references, as Excel automatically apply the right formula!
这样，您甚至不需要担心引用，因为 Excel 会自动应用正确的公式！
