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 ?
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
Transpose is missing in the last row of your code
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
Transpose has some limitations when used on large ranges.
The next version is your corrected code, but avoiding using ReDim Preserve for each iteration, which stresses the system memory if used too often:
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
Redim Preserve... Using Redim before the loop. I will edit the answer to show it.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
ReDim Preserveused often is a kind of memory stress...Resizeto get this workingResizemust be used, anyhow... I will post an answer only to see what I wonted suggesting.