excel - 将数组输出到范围的快速方法

Question

如何将数组变量输出到从表格（“test”）.range（“a1”）开始的范围内？是否有任何方法可以将数组输出到 NamedRange 而无需先前转储到单元格中？

有没有不循环的方法？

更新。数组中的元素数量不是恒定的，并且会在刷新或用户输入时发生变化。我的问题的重点是如何调整范围以使其适合数组。

标签： excelvba

Answer 1

一维数组的简单示例

数组到行：

Sub arrayTest()
    Dim arr(1 To 3) As String
    Dim r1 As Range
    Set r1 = Range("A1:C1")

    arr(1) = "larry"
    arr(2) = "moe"
    arr(3) = "curly"

    r1 = arr
End Sub

或数组到列：

Sub arrayTest2()
    Dim arr(1 To 3) As String
    Dim r1 As Range
    Set r1 = Range("A1:A3")

    arr(1) = "larry"
    arr(2) = "moe"
    arr(3) = "curly"

    r1 = Application.WorksheetFunction.Transpose(arr)
End Sub

二维数组同样简单：

Sub arrayTest3()
    Dim arr(1 To 3, 1 To 2) As String
    Dim r1 As Range
    Set r1 = Range("A1:B3")

    arr(1, 1) = "larry"
    arr(2, 1) = "moe"
    arr(3, 1) = "curly"
    arr(1, 2) = "gary"
    arr(2, 2) = "student"
    arr(3, 2) = "whatever"

    r1 = arr

End Sub

编辑＃1：

假设我们有一个任意的一维数组，从零开始或从一开始，我们希望将它推入以单元格B9开头的一行：

Sub arrayTest4()
    Dim r1 As Range, U As Long, L As Long, rBase As Range
    Set rBase = Range("B9")
    arr = Array("qwert", 1, 2, 3, 4, "ytrew", "mjiop", "nhy789")

    L = LBound(arr)
    U = UBound(arr)
    Set r1 = rBase.Resize(1, U - L + 1)

    r1 = arr

End Sub