Excel VBA - 1004 运行时错误、应用程序或对象定义错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6656385/
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 - 1004 run-time error, Application or object-defined error
提问by Kenny Bones
I'm trying to go through a range of cells in a worksheet and write a formula in each one. But I keep getting the error:
我试图浏览工作表中的一系列单元格并在每个单元格中编写一个公式。但我不断收到错误消息：
Run-time error '1004'
Application-defined or object-defined error
The code looks like this right now:
代码现在看起来像这样：
Sub updateFormulasForNamedRange()
'Application.Calculation = xlCalculationManual
'Application.ScreenUpdating = False
Dim row, col, fieldCount As Integer
colCount = 13
RowCount = 60
For col = 1 To colCount
For row = 1 To RowCount
Dim strColCharacter
If col > 26 Then
strColCharacter = Chr(Int((row - 1) / 26) + 64) & Chr(((row - 1) Mod 26) + 65)
Else
strColCharacter = Chr(row + 64)
End If
Worksheets("Rawdata1").Cells(row, col).FormulaR1C1 = "=IF(Numbers1!$E$" & col & "<>0;Numbers1!" & "$" & strColCharacter & "$" & row & ";"""")"
Next row
Next col
'Application.Calculation = xlCalculationAutomatic
'Application.ScreenUpdating = True
End Sub
It fails at the line where you assign the formula to the cell. I tried to replace the string with just "test" and it works. But it's this string that's not accepted. Even though it's the exact same string that's currently in the formula bar of that exact cell. And the string looks fine to me?
它在您将公式分配给单元格的行失败。我试图用“测试”替换字符串并且它有效。但正是这个字符串不被接受。即使它与当前在该确切单元格的编辑栏中的字符串完全相同。字符串对我来说看起来不错？
"=IF(Numbers1!$E<>0;Numbers1!$A;"")"
I don't quite know the difference of all the Formula properties, but I've tried a variant of them and all throw the same error. So what could be causing this error?
我不太清楚所有公式属性的区别，但我尝试了它们的变体并且都抛出了相同的错误。那么是什么导致了这个错误呢？
回答by Stewbob
Your problem is with .FormulaR1C1. This tells the formula to expect a Row Number, Column Number style formula reference, but then you give it an address (Column, Row) style formula.
你的问题是 .FormulaR1C1。这告诉公式需要一个行号、列号样式的公式引用，然后你给它一个地址（列、行）样式的公式。
Change .FormulaR1C1 to .Formula
将 .FormulaR1C1 更改为 .Formula
回答by Kenny Bones
Error was in the string:
错误在字符串中：
Worksheets("Rawdata1").Cells(row, col).FormulaR1C1 = "=IF(Numbers1!$E$" & col & "<>0;Numbers1!" & "$" & strColCharacter & "$" & row & ";"""")"
Should have been:
本来应该：
Worksheets("Rawdata1").Cells(row, col).FormulaR1C1 = "=IF(Numbers1!$E$" & row & "<>0;Numbers1!" & "$" & strColCharacter & "$" & row & ";"""")"
Targeting the row, not the column.
定位行，而不是列。
