如何粘贴整个数组而不在VBA中遍历整个数组? [英] How to paste a whole array without looping through it in VBA?

问题描述

我有这段代码将填充一个数组

I have this code which will populate an array

Sub rangearray()

    Dim arr() As Variant
    Dim Rng As Range
    Dim myCell As Range
    Dim i As Integer

    Set Rng = ActiveSheet.Range("G10:G14")

    For Each myCell In Rng
        ReDim Preserve arr(i)
        arr(i) = myCell
        i = i + 1
    Next myCell

    ActiveSheet.Range("H10:H14") = arr()

End Sub

在这里您可以看到监视窗口中的值是已加载的内容

Here you can see that the values in the watch window are what has been loaded in

除了，当我将数组添加回工作簿时，它只会粘贴回数组的第一个元素.

Except, when I add the array back to the workbook it only pastes back the first element of the array.

是否可以将整个数组粘贴到工作表而不必遍历整个数组?

Is it possible to paste the whole array to the worksheet without having to loop through the array?

在查看了Sorceri的链接之后，我已修改为使用.Transpose函数的代码，因此我的修改后的代码如下所示:

After taking a look at the link from Sorceri, I have amended to code to use the .Transpose function, so my amended code now look like this:

Sub rangearray()

    Dim arr() As Variant
    Dim Rng As Range
    Dim myCell As Range
    Dim i As Integer

    Set Rng = ActiveSheet.Range("A1:A5")

    For Each myCell In Rng
        ReDim Preserve arr(i)
        arr(i) = myCell
        i = i + 1
    Next myCell

    ActiveSheet.Range("B1:B5") = WorksheetFunction.Transpose(arr)

End Sub

推荐答案

您将要使用转置工作表功能

you will want to use the transpose worksheet function http://msdn.microsoft.com/en-us/library/office/ff196261.aspx

请参阅下文.您必须将其分配给范围值

See below. You have to assign it to the range's value

Sub rangearray()

Dim arr() As Variant
Dim Rng As Range
Dim myCell As Range
Dim i As Integer

Set Rng = ActiveSheet.Range("A1:A5")

For Each myCell In Rng
    ReDim Preserve arr(i)
    arr(i) = myCell
    i = i + 1
Next myCell

ActiveSheet.Range("B1:B5").Value = WorksheetFunction.Transpose(arr)

End Sub

这篇关于如何粘贴整个数组而不在VBA中遍历整个数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！