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

VBA - Run Time Error 1004 'Application Defined or Object Defined Error'

提问by user3105671

I have an Excel document that copies a template sheet into a new sheet on the first time it runs. Any more sheets that follow this template are appended to the newly created sheet.

我有一个 Excel 文档，它在第一次运行时将模板工作表复制到新工作表中。遵循此模板的任何更多工作表都会附加到新创建的工作表中。

I'm getting the error in the title in this section of code:

我在这部分代码的标题中收到错误：

If Worksheets("User Configuration").Cells(9, 15).Value = 1 Then
  Worksheets("Cable Cards Template").Range("A1:J33").Copy

  With Worksheets("Cable Cards")
  **.Range(Cells(RangeStartRow, RangeStartColumn), Cells(RangeEndRow, RangeEndColumn)).PasteSpecial xlValues**
    .Range(Cells(RangeStartRow, RangeStartColumn), Cells(RangeEndRow, RangeEndColumn)).PasteSpecial xlFormats
  End With

  Worksheets("Cable Cards Template").Shapes("Picture 1").Copy
  Worksheets("Cable Cards").Paste Cells(RangeStartRow, RangeStartColumn)

  Call Sheets.FormatCableCardRows      
End If

Basically if the Ifstatement is true (the cell = 1), then a range on a particular sheet should be copied and pasted into the new sheet at the range given using PasteSpecialfor values and formatting. Following that, the "newly created" sheet should have an image copied into the top left cell of the template and then a subroutine is called to format the rows of the new sheet.

基本上，如果该If语句为真（单元格 = 1），则应将特定工作表上的范围复制并粘贴到新工作表中使用PasteSpecial值和格式给定的范围内。之后，“新创建的”工作表应将图像复制到模板的左上角单元格中，然后调用子例程来格式化新工作表的行。

I'm getting the error at the first .Rangecall after the With Worksheets("Cable Cards")statement. I've tried not using the Withstatement, copying values directly instead of paste-special etc. The weird thing is that this will run on the first go, when the new sheet is created via:

我.Range在With Worksheets("Cable Cards")语句后的第一次调用时收到错误。我试过不使用该With语句，直接复制值而不是特殊粘贴等。奇怪的是，当通过以下方式创建新工作表时，这将在第一次运行时运行：

If (RangeStartRow = 1) Then
  Worksheets.Add().Name = "Cable Cards" ' Create new sheet with given name only on first cable card
  Columns(1).ColumnWidth = 9.43
  Columns(6).ColumnWidth = 11
  Columns(10).ColumnWidth = 9
  Call FormatForA5Printing("Cable Cards", 71)
End If

but on the 2nd go, it fails entirely, with the Run Time Error 1004 'Application Defined or Object Defined Error'. I'd appreciate any help.

但是在第二次运行时，它完全失败了，Run Time Error 1004 'Application Defined or Object Defined Error'. 我很感激任何帮助。

回答by Siddharth Rout

Your cells object is not fully qualified. You need to add a DOTbefore the cells object. For example

您的单元格对象不完全合格。您需要DOT在单元格对象之前添加一个。例如

With Worksheets("Cable Cards")
    .Range(.Cells(RangeStartRow, RangeStartColumn), _
           .Cells(RangeEndRow, RangeEndColumn)).PasteSpecial xlValues

Similarly, fully qualify all your Cells object.

同样，完全限定所有 Cells 对象。

回答by sancho.s ReinstateMonicaCellio

Solution #1: Your statement

解决方案#1：你的陈述

.Range(Cells(RangeStartRow, RangeStartColumn), Cells(RangeEndRow, RangeEndColumn)).PasteSpecial xlValues

does not refer to a proper Rangeto act upon. Instead,

不是指Range行为的正当性。反而，

.Range(.Cells(RangeStartRow, RangeStartColumn), .Cells(RangeEndRow, RangeEndColumn)).PasteSpecial xlValues

does (and similarly in some other cases).

确实（在其他一些情况下也是如此）。

Solution #2: Activate Worksheets("Cable Cards")prior to using its cells.

解决方案#2：Worksheets("Cable Cards")在使用其细胞之前激活。

Explanation: Cells(RangeStartRow, RangeStartColumn)(e.g.) gives you a Range, that wouldbe ok, and that is why you often see Cellsused in this way. But since it is not applied to a specific object, it applies to the ActiveSheet. Thus, your code attempts using .Range(rng1, rng2), where .Rangeis a method of one Worksheetobject and rng1and rng2are in a different Worksheet.

解释： Cells(RangeStartRow, RangeStartColumn)(eg) 给你一个Range，那就可以了，这就是为什么你经常看到Cells以这种方式使用。但由于它不适用于特定对象，因此它适用于ActiveSheet. 因此，您的代码尝试使用.Range(rng1, rng2), where.Range是一个Worksheet对象的方法，而rng1和rng2在不同的Worksheet.

There are two checks that you can do to make this quite evident:

您可以执行两项检查来使这一点非常明显：

1. Activate your Worksheets("Cable Cards")prior to executing your Suband it will start working (now you have well-formed references to Ranges). For the code you posted, adding .Activateright after With...would indeed be a solution, although you might have a similar problem somewhere else in your code when referring to a Rangein another Worksheet.

2. With a sheet other than Worksheets("Cable Cards")active, set a breakpoint at the line throwing the error, start your Sub, and when execution breaks, write at the immediate window

   Debug.Print Cells(RangeStartRow, RangeStartColumn).Address(external:=True)

   Debug.Print .Cells(RangeStartRow, RangeStartColumn).Address(external:=True)

   and see the different outcomes.

1. Worksheets("Cable Cards")在执行之前激活你的Sub，它将开始工作（现在你有对Ranges 的格式良好的引用）。对于您发布的代码，.Activate在之后添加With...确实是一个解决方案，尽管在引用Rangeanother 中的a 时，您的代码中的其他地方可能会遇到类似的问题Worksheet。

2. 使用非Worksheets("Cable Cards")活动表，在抛出错误的行设置断点，启动您的Sub，当执行中断时，在立即窗口写入

   Debug.Print Cells(RangeStartRow, RangeStartColumn).Address(external:=True)

   Debug.Print .Cells(RangeStartRow, RangeStartColumn).Address(external:=True)

   并看到不同的结果。

Conclusion: Using Cellsor Rangewithout a specified object (e.g., Worksheet, or Range) might be dangerous, especially when working with more than one Sheet, unless one is quite sure about what Sheetis active.

结论：使用Cells或Range不使用指定的对象（例如，Worksheet， 或Range）可能是危险的，尤其是在使用多个 时Sheet，除非非常确定什么Sheet是活动的。

回答by Mosca Pt

Assgining a value that starts with a "=" will kick in formula evaluation and gave in my case the above mentioned error #1004. Prepending it with a space was the ticket for me.

分配以“=”开头的值将启动公式评估，并在我的情况下给出上述错误 #1004。在它前面加上一个空格对我来说是一张票。