Excel VBA - 1004 运行时错误、应用程序或对象定义错误 [英] Excel VBA - 1004 run-time error, Application or object-defined error

问题描述

我正在尝试查看工作表中的一系列单元格，并在每个单元格中编写一个公式.但我不断收到错误:

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

代码现在是这样的:

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$1<>0;Numbers1!$A$1;"")"

我不太清楚所有公式属性的区别，但我尝试了它们的一个变体并且都抛出相同的错误.那么是什么导致了这个错误?

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?

推荐答案

Error was in the string:

Error was in the string:

Worksheets("Rawdata1").Cells(row, col).FormulaR1C1 = "=IF(Numbers1!$E$" & col & "<>0;Numbers1!" & "$" & strColCharacter & "$" & row & ";"""")"

应该是:

Worksheets("Rawdata1").Cells(row, col).FormulaR1C1 = "=IF(Numbers1!$E$" & row & "<>0;Numbers1!" & "$" & strColCharacter & "$" & row & ";"""")"

定位到行，而不是列.

这篇关于Excel VBA - 1004 运行时错误、应用程序或对象定义错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！