VBA错误'方法范围..失败“(通过变量声明的范围)

Question

>我在两张纸上找到最后一行和最后一列，并将这些值分配给变量。然后，我使用这些变量来形成一个范围的边界，该范围被放入 VBA 数组中。

当我定位在引用中使用的工作表以外的工作表上时，为什么会出现错误（"对象'_Worksheet'的方法'范围'失败"？我正在使用代号。

代码如下：

Private arrPlan() As Variant
Private lastRowSource As Long
Private lastColSource As Long
Private arrRawData() As Variant
Private lastRowDestination As Long
Private lastColDestination As Long
Private arrStrings() As Variant
Private str As String
Public Sub Google_Plan_Into_RawData()

'------------------------ Read Excel ranges into Arrays -----------------
lastRowSource = Sheet1.Range("A" & Rows.count).End(xlUp).Row
lastColSource = Sheet1.Range("A1").End(xlToRight).Column
arrPlan = Sheet1.Range(Cells(1, 1), Cells(lastRowSource, lastColSource))

lastColDestination = Sheet2.Range("A1").End(xlToRight).Column
lastRowDestination = Sheet2.Range("A" & Rows.count).End(xlUp).Row
arrRawData = Sheet2.Range(Cells(1, 1), Cells(lastRowDestination, lastColDestination))
// ...Rest of the code

它在此行失败：
arrPlan = Sheet1.Range(Cells(1, 1), Cells(lastRowSource, lastColSource)) 如果我在 Sheet2 上，当然，它在以下行失败：

arrRawData = Sheet2.Range(Cells(1, 1), Cells(lastRowDestination, lastColDestination))如果我在工作表 1 上。

我通常做的是：

With Sheet1
lastRowSource = .Range("A" &Rows.count).End(xlUp).Row
lastColSource= .Range("A1").End(xlToRight).Column
arrPlan = .Range(Cells(1, 1), Cells(lastRowSource, lastColSource))
End With. 

还是不舔。我在这里做错了什么？

Answer 1

尝试使用完整的范围引用，包括工作表代号：

arrPlan = Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(lastRowSource, lastColSource))
arrRawData = Sheet2.Range(Sheet2.Cells(1, 1), Sheet2.Cells(lastRowDestination, lastColDestination))

Answer 2

尝试将工作簿和工作表的引用放在范围内：

arrPlan = Range(Sheet1.Cells(1, 1), Sheet1.Cells(lastRowSource, lastColSource)) if 

表 2：

arrRawData = Range(Sheet2.Cells(1, 1), Sheet2.Cells(lastRowDestination, lastColDestination))