vba 如何粘贴整个数组而不在VBA中循环遍历它？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19300555/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to paste a whole array without looping through it in VBA?
提问by spences10
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:
查看来自 Sorceri 的链接后，我已修改代码以使用 .Transpose 函数，因此我修改后的代码现在如下所示：
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
回答by Sorceri
you will want to use the transpose worksheet function http://msdn.microsoft.com/en-us/library/office/ff196261.aspx
您将要使用转置工作表功能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
回答by Siddharth Rout
As I mentioned in my comment above that you do not need an array to perform the action that you trying to do but still if you want only an array solution then you don't need to go the long way of filling the array in a loop. Directly assign the range's value to the array. It will create a 2D array which you don't need to transpose.
正如我在上面的评论中提到的，您不需要数组来执行您尝试执行的操作，但是如果您只想要一个数组解决方案，那么您不需要走很长的路来在循环中填充数组. 直接将范围的值分配给数组。它将创建一个不需要转置的二维数组。
Sub rangearray()
Dim arr
Dim Rng As Range
With ActiveSheet
Set Rng = ActiveSheet.Range("G10:G14")
arr = Rng.Value
.Range("H10").Resize(UBound(arr, 1)).Value = arr
End With
End Sub
回答by Dinesh Madhup
Just assigned Rng to arr then put back to sheet. it works for me in Excel 2016
刚刚将 Rng 分配给 arr 然后放回工作表。它在 Excel 2016 中对我有用
Sub rangearray()
Dim arr() As Variant
Dim Rng As Range
Dim myCell As Range
Dim i As Integer
Set Rng = ActiveSheet.Range("A1:A5")
arr = Rng
ActiveSheet.Range("B1:B5").Resize(5) = arr
End Sub
