excel - 将数组粘贴到范围仅粘贴第一个值

Question

我正在尝试将数组值粘贴到一个范围内，但它只将第一个值粘贴到范围内。

Dim Array1()

For i = 0 to 9
   ReDim Preserve Array1
   Array1(i)= Int((6 * Rnd) + 1)
Next

ws.Range("A1").Value = Array1

输出:

我是不是做错了什么？

Answer 1

您必须正确调整数组的尺寸，然后转置它。

Option Explicit

Sub Sample()
    Dim Array1()
    Dim ws As Worksheet
    Dim i As Long
    
    '~~> Change to respective sheet
    Set ws = Sheet1

    For i = 0 To 9
        ReDim Preserve Array1(i) '<~~ increment by i
        Array1(i) = Int((6 * Rnd) + 1)
    Next
    
    '~~> Store in the worksheet
    ws.Range("A1").Resize(UBound(Array1) + 1, 1).Value = Application.Transpose(Array1)
End Sub