I am finding last row and last column on two sheets and assigning these values to variables. Then, I use these variables to form bounds of a range which gets put into a VBA array.
Why am I getting an error ("Method 'Range' of object '_Worksheet' failed") when I'm positioned on a sheet other than the one used in the reference? I am using codenames.
Here's the code:
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
It fails on this line:arrPlan = Sheet1.Range(Cells(1, 1), Cells(lastRowSource, lastColSource))
if I'm on Sheet2 and of course, it fails on this line:arrRawData = Sheet2.Range(Cells(1, 1), Cells(lastRowDestination, lastColDestination))
if I'm on Sheet1.
What I usually do is:
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.
Still no lick. What am I doing wrong here?
