Populating an array using range)Cells

Question

I get a Run-Time error 1004 if I run the sub with the commented out lines active. It runs fine as is, but I need to increment the array to read more data in another loop. I'd prefer to use the Range(Cells option.

Option Explicit

Dim myarray As Variant

'There are 6 numbers stored in a1 to a6 on sheet1

Sub read_as_entire_()
    'This line fails if I use Cells...
    'myarray = Worksheets("Sheet2").Range(Cells(1, 1), Cells(6, 1)).Value

    'This line works fine
     myarray = Worksheets("Sheet2").Range("a1:a6").Value

    Sheet2.Range("b1:b6") = myarray

    'Sheet2.Range(Cells(1, 2), Cells(6, 2)) = myarray

End Sub

What's the difference?

Answer 1

I suspect that when you refer to Cells in the commented out section that it is not referring to Sheet2. It is probably referring to the active sheet instead.

To test this try modifying the line to the following:

With Worksheets("Sheet2")
    myarray = .Range(.Cells(1, 1), .Cells(6, 1)).Value
End With

I'm using a with construct which means when I use the dot ('.') in that formula it is referring to the object referenced at the beginning of the nearest with statement block.

Answer 2

the "Cells" refer to a range of the active sheet (which is not "Sheet2" or else it would work), the Worksheets("Sheet2").Range only accepts Ranges of the "Sheet2" Worksheet, so it raises an error. you can fix this with:

myarray = Worksheets("Sheet2").Range(Worksheets("Sheet2").Cells(1, 1), Worksheets("Sheet2").Cells(6, 1)).Value

or shorter

with Worksheets("Sheet2")
    myarray = .Range(.Cells(1, 1), .Cells(6, 1)).Value
end with

i prefer to use

myarray = Worksheets("Sheet2").Cells(1, 1).Resize(6,1)