尝试访问工作表范围时出现VBA运行时错误1004 [英] VBA Runtime error 1004 when trying to access range of sheet

问题描述

我正在构建一个小的vba脚本，它将多个工作簿中的表合并到另一个工作簿的一个工作表中.当我尝试设置目标范围的值时会引发错误:

I am building a small vba script that is merging tables from several workbook into one single worksheet of another workbook. The error is raised when I try to set the destination range's value:

wksPivotData.Range(wksPivotData.Cells(CurrentRow, 1)).Resize(tbl.ListRows.Count, tbl.ListColumns.Count).Value = _
    tbl.Range.Value

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

The error: "Run-time error '1004': Application-Defined or object-defined error"

我遇到了类似的问题，总的答案是我在

I went through similar questions, and the general answer is what I found in this one: The selected cell belongs to another worksheet than the one desired.

尽管这完全有道理，但我仍然无法弄清楚为什么我的代码会中断，因为我仅使用数字引用( CurrentRow 是Long)和 Resize ，应该可以防止我犯这样的错误.

While this makes complete sense, I still can't figure why my code breaks as I'm only using numerical reference (CurrentRow is a Long) and Resize, which should prevent me from doing such a mistake.

此外，我在即时"窗口中进行了一些快速测试，结果发现，尽管工作表 wksPivotData 存在，并且我可以访问其名称和单元格值，但range函数根本不起作用工作:

Additionally, I ran a couple quick tests in the Immediate window and it turns out that while the worksheet wksPivotData exists and I can access its name and a cell value, the range function simply doesn't work:

Debug.Print wksPivotData.Name
    PivotData

Debug.Print wksPivotData.Cells(1, 1).Value
    123

这两种方法都可以，但是下一个却不能:

Both of those work but the next one doesn't:

Debug.Print wksPivotData.Range(1, 1).Value

推荐答案

您的最后一行 Debug.Print wksPivotData.Range(1，1).Value 将不会打印，因为您使用的是错误的 Range().我想你要A1吗?

Your last line, Debug.Print wksPivotData.Range(1, 1).Value won't print because you're misuing Range(). I assume you want A1?

使用 Range(1,1)时，是指不存在的范围.如果要处理单元格A1，则需要

When using Range(1,1), you're referring to a non-existent range. If you want to do cell A1, you need

With wksPivotData
   myData = .Range(.Cells(1,1),.Cells(1,1)).Value
End with

由于您使用的是多个工作表，因此如上所述，我将使用 with 语句.编写相同内容的另一种方法是 wksPivotData.Range(wksPivotData.Cells(1,1)，wksPivotData.Cells(1,1))(您需要明确告知Excel您要引用的工作表到使用 Range()和 cells()的时候.

Since you're using multiple worksheets, I'd use the with statement as above. Another way to write the same thing is wksPivotData.Range(wksPivotData.Cells(1,1),wksPivotData.Cells(1,1)) (You need to explicitly tell Excel what sheet you want to refer to when using Range() and cells().

最后，为调整大小，如果我没记错的话，您将必须在您的范围内两次添加相同的 Cell():

Finally, for your resize, if I recall correctly, you're going to have to add the same Cell() twice in your range:

wksPivotData.Range(wksPivotData.Cells(CurrentRow, 1),ksPivotData.Cells(CurrentRow, 1)).Resize(tbl.ListRows.Count, tbl.ListColumns.Count).Value = _
    tbl.Range.Value

或者，对于同一件事，但执行方式不同:

Or, for the same thing, but different way of doing it:

With wksPivotData
    .Range(.Cells(currentRow, 1), .Cells(currentRow, 1)).Resize(tbl.ListedRows.Count, tbl.ListColumns.Count).Value = tbl.Range.Value
End With

这篇关于尝试访问工作表范围时出现VBA运行时错误1004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！