粘贴VBA数组 [英] Paste vba array

问题描述

我有这样的code，将填充数组

 子rangearray（）    昏暗ARR（）为Variant
    昏暗RNG由于范围
    暗淡了myCell由于范围
    昏暗我作为整数    设置RNG = ActiveSheet.Range（G10：G14）    对于每一个在了myCell RNG
        使用ReDim preserve ARR（I）
        ARR（I）=了myCell
        I = I + 1
    下一步了myCell    ActiveSheet.Range（H10：H14）= ARR（）结束小组
 

在这里，你可以看到，在监视窗口中的值是什么已加载

除非，当我添加了数组回工作簿只贴回数组的第一个元素。

是否有可能整个阵列工作表粘贴不通过阵列不必循环？

更新考虑看看从Sorceri的链接后我已经修改为code使用.Transpose功能，所以我修改code现在看起来是这样的：

 子rangearray（）    昏暗ARR（）为Variant
    昏暗RNG由于范围
    暗淡了myCell由于范围
    昏暗我作为整数    设置RNG = ActiveSheet.Range（A1：A5）    对于每一个在了myCell RNG
        使用ReDim preserve ARR（I）
        ARR（I）=了myCell
        I = I + 1
    下一步了myCell    ActiveSheet.Range（B1：B5）= WorksheetFunction.Transpose（ARR）结束小组
 

解决方案

您将要使用的转置工作表函数的 http://msdn.microsoft.com/en-us/library/office/ff196261.aspx

见下文。你必须将其分配到的范围内的值

 子rangearray（）昏暗ARR（）为Variant
昏暗RNG由于范围
暗淡了myCell由于范围
昏暗我作为整数设置RNG = ActiveSheet.Range（A1：A5）对于每一个在了myCell RNG
    使用ReDim preserve ARR（I）
    ARR（I）=了myCell
    I = I + 1
下一步了myCellActiveSheet.Range（B1：B5）。值= WorksheetFunction.Transpose（ARR）结束小组
 

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?

UPDATE 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屋！