Excel VBA 错误 1004 - 应用程序定义或对象定义的错误，在我的公式中使用双引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19740392/
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 Error 1004 - Application-defined or object-defined error with double quotes in my formula
提问by wedwo
I'm trying to add a formula using VBA to a cell in Excel, the (simplified) code is as
我正在尝试使用 VBA 将公式添加到 Excel 中的单元格，（简化）代码如下
Dim destRow as integer
destRow = 107
Cells(destRow, 19) = "=IF(L" & destRow & "="""";"""";""UNB"")"
`I've also tried the following (in place of the last line):
`我还尝试了以下操作（代替最后一行）：
Cells(destRow, 19) = "=IF(L" & destRow & "=" & chr(34) & chr(34) & ";" & chr(34) & chr(34) & ";" & chr(34) & "UNB" & chr(34) & ")"
In the immediate window, both produce the correct output:
在立即窗口中，两者都产生正确的输出：
=IF(L107="";"";"UNB")
...however, I get error 1004 "Application-defined or object-defined error" on the last line when I run my code.
...但是，当我运行我的代码时，我在最后一行收到错误 1004“应用程序定义或对象定义的错误”。
Any help would be appreciated!
任何帮助，将不胜感激！
回答by BrainO2
You should replace the semicolons ";" in the IF formula for commas ",". They will appear as semicolons depending on your excel configuration when the formula is interpreted.
您应该替换分号“;” 在逗号“,”的IF公式中。在解释公式时，它们将显示为分号，具体取决于您的 excel 配置。
回答by RobinL
I think you probably just need to refer to the relevant worksheet.
我想你可能只需要参考相关的工作表。
Your code would work if it was in the code for a sheet rather than in a module.
如果您的代码在工作表的代码中而不是在模块中，它就会起作用。
So
所以
Sheet1.Cells(destRow, 19) = "=IF(L" & destRow & "=" & chr(34) & chr(34) & ";" & chr(34) & chr(34) & ";" & chr(34) & "UNB" & chr(34) & ")"
Obviously substitute for the desired sheet if it's not Sheet1
如果不是 Sheet1，显然替换所需的工作表
