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?

Siphor · Accepted Answer · 2015-04-11 21:22:04Z

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)

answered Apr 11, 2015 at 21:22

Siphor

2,5442 gold badges15 silver badges10 bronze badges

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

Comments

Rossco · Accepted Answer · 2015-04-11 21:20:38Z

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.

answered Apr 11, 2015 at 21:20

Rossco

1,14211 silver badges26 bronze badges

Collectives™ on Stack Overflow

Populating an array using range)Cells

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related