vba遍历数组_如何粘贴整个数组而不在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?

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

解决方案

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