使用范围填充数组） [英] Populating an array using range)Cells

问题描述

如果运行带有注释行的子程序，则会收到运行时错误1004。
可以正常运行，但是我需要增加数组以在另一个循环中读取更多数据。我更喜欢使用Range（Cells选项。

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

有什么区别？

推荐答案

单元格是指活动工作表的范围（不是 Sheet2 ，否则它将正常工作），Worksheets（ Sheet2）。Range仅接受 Sheet2工作表的范围，因此会引发错误。您可以使用以下方法解决此问题：

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

或更短

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

我更喜欢使用

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

这篇关于使用范围填充数组）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！