如何从Excel中的工作表列填充数组 [英] How to populate array from a sheet column in Excel

问题描述

我有一个像这样的数组:

I have an array like this :

myColumns = Array("Serial","Practice","Manager", "QTD")

但是我想从工作表中获取其值以使其更具动态性. (值及其数量可能会有所不同)

But I want to fetch its values from a sheet to make it more dynamic. (The values & their number may vary)

所以我尝试过这样来影响从A2到列的最后一个值到我的数组的范围:

So I tried this to affect the range from A2 to last value of the column to my array:

myColumns = Range(Range("A2"), Range("A2").End(xlDown)).Value

结果为:

UBound(myColumns) -> 4 -> OK

但是当我这样做时:

s = myColumns(3) -> Subscribe out of range ! 

那怎么可能?

如何正确填充? 谢谢！

How can I populate it correctly? Thank you!

推荐答案

Application.Transpose是您的好朋友，如果您正在分析一列:

Application.Transpose is a good friend of yours, if you are parsing a single column:

Sub TestMe1()
    Dim myArr As Variant
    myArr = Application.Transpose(Range("A1:A10"))

    Dim cnt As Long
    For cnt = LBound(myArr) To UBound(myArr)
        Debug.Print myArr(cnt)
    Next cnt

End Sub

如果您要解析一行，则应转置两次:

If you are parsing a single row, you should transpose twice:

Sub TestMe2()
    Dim myArr As Variant
    With Application
        myArr = .Transpose(.Transpose(Range("A1:AI1")))
    End With
End Sub

如果只是解析多个范围或不使用.Transpose()，则必须同时引用列和行:

If you are simply parsing multiple range or without .Transpose(), you have to refer to both columns and rows:

• 我写过一篇关于它的文章前段时间.

这篇关于如何从Excel中的工作表列填充数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！