VBA array cells formula: error 1004 application-defined or object-defined error

[JPF]

Hi all-

Why does the following code cause the run-time error unless I first select sheet(1)?

my_array = Sheets(1).Range(Cells(1, 1), Cells(3, 1)).Resize(3,  1)

And, related, why does the following code work (no error) regardless of which sheet is selected?

my_array =  Sheets(1).Range("a1:a3").Resize(3, 1)

I do not understand why hard coding the range with "a1:a3" should be different than using the "cells" method.

In both cases, I am using

Dim my_array As Variant 
ReDim  my_array(1 To 3, 1)

I am using Excel 2003 SP3 with Windows XP.

-Jason

 

[VoG]

You need to fully qualify the sheet reference:

my_array = Sheets(1).Range(Sheets(1).Cells(1, 1), Sheets(1).Cells(3, 1)).Resize(3, 1)

 

[JPF]

Fantastic! Much obliged, Peter.

-Jason