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

问题描述

我试图通过一个工作表中的单元格，并在每个单元格中写一个公式。
但是我不断收到错误：

 运行时错误'1004'
 
应用程序定义或对象定义的错误
  

代码看起来像这样：

  Sub updateFormulasForNamedRange（）
'Application.Calculation = xlCalculationManual 
'Application.ScreenUpdating = False 
 
 Dim row，col，fieldCount As Integer 
 colCount = 13 
 RowCount = 60 
 
对于col = 1到colCount 
对于row = 1到RowCount 
 Dim strColCharacter 
 
如果col> 26然后
 strColCharacter = Chr（Int（（row-1）/ 26）+ 64）& Chr（（（row-1）Mod 26）+ 65）
 Else 
 strColCharacter = Chr（row + 64）
 End If 
 
工作表（Rawdata1 ）.Cells（row，col）.FormulaR1C1 == IF（Numbers1！$ E $& col&0; Numbers1！&$& strColCharacter&$& ; row&;）
 
下一行
下一个col 
 
'Application.Calculation = xlCalculationAutomatic 
'Application.ScreenUpdating = True 
 End Sub 
  

在将公式分配给单元格的行失败。我试图用测试替换字符串，它可以工作。但是这个字符串是不被接受的。即使它是当前在该精确单元格的公式栏中的完全相同的字符串。
我的字符串看起来很好吗？

 = IF（Numbers1！$ E $ 1<> 0 ;数字1！$ A $ 1;）
  

我不太了解所有的差异公式属性，但我已经尝试了它们的变体，并且都抛出相同的错误。那么可能导致这个错误？

解决方案

错误在字符串中：

 工作表（Rawdata1）。单元格（行，列）.FormulaR1C1 == IF（Numbers1！$ E $& col&0 ; Numbers1！&$& strColCharacter&$&&;）
  

应该是：

 工作表（Rawdata1）。 .FormulaR1C1 == IF（Numbers1！$ E $&&0; Numbers1！&$& strColCharacter&$& row& ）
  

定位行而不是列。

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$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:

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.

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