CODEWITHSUNDEEP
excelvba
TourEiffel
TourEiffel

Reputation: 4414

Paste array to range paste only the first value

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 :

enter image description here

Am I doing wrong something ?

Upvotes: 3

Views: 592

Answers (3)

FaneDuru
FaneDuru

Reputation: 42236

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

Upvotes: 3

VBasic2008
VBasic2008

Reputation: 54807

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

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149297

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

Upvotes: 4

Related Questions