Populate cells with arrays
 

Hi:


I have 50 arrays with 30 elements each. On a sheet, it
would look like a 50rows x 30columns table.
For now, I can live with the row loops. But, I really need
a way so I can populate my columns (Ax:ADx) with my array
elements without doing the column loops. What I have right
now is a very simple column loop but hurts the performance
really bad.

Range("A1").Activate
For i = 1 To 30
ActiveCell.Value = Array1(i)
ActiveCell.Next.Activate
Next i

Does anyone have a better solution?


Regards,
Adrian T

Populate cells with arrays
 

One way:

If your array is a Variant:

Range("A1:AD1").Value = Array1


In article ,
"Adrian T" wrote:

Hi:


I have 50 arrays with 30 elements each. On a sheet, it
would look like a 50rows x 30columns table.
For now, I can live with the row loops. But, I really need
a way so I can populate my columns (Ax:ADx) with my array
elements without doing the column loops. What I have right
now is a very simple column loop but hurts the performance
really bad.

Range("A1").Activate
For i = 1 To 30
ActiveCell.Value = Array1(i)
ActiveCell.Next.Activate
Next i

Does anyone have a better solution?


Regards,
Adrian T

Populate cells with arrays
 

Adrian,

You can assign the values in an array directly to a range:

Range("A1").Resize(50, 1).Value = Application.Transpose(Array1)

The transpose is necessary as the array elements are normally treated as
being in a row.

It would be even more efficient to use a single two dimensinal array with 50
rows and 30 columns, which can be dirrectly assigned with no need to loop
through the columns.

Dim Array1(1 To 50, 1 To 30) As Double

Range("A1").Resize(50, 30).Value = Array1


John Green



"Adrian T" wrote in message
...
Hi:


I have 50 arrays with 30 elements each. On a sheet, it
would look like a 50rows x 30columns table.
For now, I can live with the row loops. But, I really need
a way so I can populate my columns (Ax:ADx) with my array
elements without doing the column loops. What I have right
now is a very simple column loop but hurts the performance
really bad.

Range("A1").Activate
For i = 1 To 30
ActiveCell.Value = Array1(i)
ActiveCell.Next.Activate
Next i

Does anyone have a better solution?


Regards,
Adrian T

Populate cells with arrays
 

If you have the 50X30 elements in a two dimensional 50-row by 30-column
array called MyArray, the 4th row vector can be transferred with, e.g.,

Range("A4:AD4").Value = Application.Index(MyArray,4,0)

Alan Beban

John Green wrote:
Adrian,

You can assign the values in an array directly to a range:

Range("A1").Resize(50, 1).Value = Application.Transpose(Array1)

The transpose is necessary as the array elements are normally treated as
being in a row.

It would be even more efficient to use a single two dimensinal array with 50
rows and 30 columns, which can be dirrectly assigned with no need to loop
through the columns.

Dim Array1(1 To 50, 1 To 30) As Double

Range("A1").Resize(50, 30).Value = Array1


John Green



"Adrian T" wrote in message
...

Hi:


I have 50 arrays with 30 elements each. On a sheet, it
would look like a 50rows x 30columns table.
For now, I can live with the row loops. But, I really need
a way so I can populate my columns (Ax:ADx) with my array
elements without doing the column loops. What I have right
now is a very simple column loop but hurts the performance
really bad.

Range("A1").Activate
For i = 1 To 30
ActiveCell.Value = Array1(i)
ActiveCell.Next.Activate
Next i

Does anyone have a better solution?


Regards,
Adrian T