Paste array to range paste only the first value-CodePudding

I am trying to paste Array values into a range but it does only paste in range the first value.

Dim Array1()

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

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

output :

Am I doing wrong something ?

CodePudding user response：

You have to redimension the array correctly and then transpose it.

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

CodePudding user response：

Please, try the next way, in order to fill an already dimensioned 2D array, easy to drop its content in a range:

Sub fill2DArray()
  Dim Array1(1 To 10, 1 To 1), ws As Worksheet, i As Long
  Set ws = ActiveSheet
    For i = 0 To 9
       Array1(i   1, 1) = Int((6 * Rnd)   1)
    Next

 ws.Range("A1").Resize(UBound(Array1), UBound(Array1, 2)).value = Array1
End Sub

The next version is your corrected code, but avoiding using Redim Preserve for each iteration:

Sub fill1DEfficient()
    Dim Array1(), i As Long, ws As Worksheet, itNo As Long
    
    Set ws = ActiveSheet
    itNo = 10 'number of iterations
    ReDim Array1(itNo - 1)
    For i = 0 To itNo - 1
       Array1(i) = Int((6 * Rnd)   1)
    Next
   ws.Range("A1").Resize(UBound(Array1)   1, 1).value = Application.Transpose(Array1)
End Sub

CodePudding user response：

One-Column Array to One-Column Range

Option Explicit

Sub WriteArrayToOneColumnRange()
    
    Const rCount As Long = 10 ' Number of Elements (rows in this case)
    Const rndMin As Long = 1
    Const rndMax As Long = 6
    
    ' Define the 2D one-based one-column array.
    Dim Data As Variant: ReDim Data(1 To rCount, 1 To 1)
    
    Dim r As Long ' Element (Row) Counter
    
    ' Populate the array.
    For r = 1 To rCount
        Data(r, 1) = Int(((rndMax - rndMin   1) * Rnd)   rndMin)
        'Debug.Print Data(r, 1)
    Next r
    
    ' Write the array values to a one-column range starting in 'A1'.
    Dim ws As Worksheet: Set ws = ActiveSheet ' be more specific
    ws.Range("A1").Resize(rCount).Value = Data

End Sub