VBA error 'Method Range of ... failed' (Range declared via variables)

Question

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?

Kazimierz Jawor · Accepted Answer · 2016-03-03 12:24:04Z

2

Try to use complete range reference including worksheets codename:

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

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

answered Mar 3, 2016 at 12:24

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Alexander Starbuck Over a year ago

Hmm... I tried something similar but normally, I do this inside a With statement. that way I shouldnt have to worry about this kind of thing, no? But I still get an error. (First one is 'With Sheet1...' and the other is 'With Sheet2...'

Kazimierz Jawor Over a year ago

if you do this with With statement then show it in your code in your question!

Alexander Starbuck Over a year ago

What I meant was - in another similar document, I do this with a With statement. I tried here and the same problem happened. So, I discarded With and did like in my question.

Kazimierz Jawor Over a year ago

strange, it is working fine for me with repeated references to Worksheets three times in each line.

Kazimierz Jawor Over a year ago

it is working for me as I presented in my answer and is not working when using code from your question.

|

juanora · Accepted Answer · 2016-03-03 12:32:42Z

0

Try to place the reference for workbook and worksheet inside the range:

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

Sheet 2:

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

answered Mar 3, 2016 at 12:32

juanora

54211 silver badges23 bronze badges

Collectives™ on Stack Overflow

VBA error 'Method Range of ... failed' (Range declared via variables)

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related