How to write an array to a range without looping

[Andy]

Hello,
I have an array MyArray(10000,3), and I want to write this into sheet
1 starting at row 1 col 1. I would like to do it without using a for
loop or a counter. I am looking for a way to paste the entire array
at once. The reason for this is processing speed. A for loop takes a
while when the array gets large.

thanks,
Andy

 

[isabelle]

hi Andy,

Sub Test()
Dim a(1 To 3, 1 To 2)

For i = 1 To 3
a(i, 1) = i
a(i, 2) = i * 3
Next i

[A1:B3] = a
End Sub

 

[joeu2004]

I have an array MyArray(10000,3), and I want to write this
into sheet 1 starting at row 1 col 1. I would like to do
it without using a for loop or a counter.

Ostensibly, simply:

Sheets("sheet1").Range("a1:c10000") = MyArray

or

Sheets("sheet1").Range("a1").resize(10000,3) = MyArray

But if you declare the array as Dim MyArray(10000,3) with the default
Option Base (0), your array indices run from 0 to 10000 and 0 to 3,
even if you use only 1 to 10000 and 1 to 3.

Consequenty, the assignment statements above will shift and truncate
your data by one row and one column.

Although you could set Option Base 1, I prefer to declare the range of
indices explicitly, e.g. Dim MyArray(1 to 10000,1 to 3).

Then the assignment statements above will have the intended result.

 

[Dave Peterson]

I would use something like this:

Option Explicit
Sub testme()
Dim myArr(1 To 5, 1 To 3) As Long
Dim rCtr As Long
Dim cCtr As Long
Dim DestCell As Range

'test data only
For rCtr = LBound(myArr, 1) To UBound(myArr, 1)
For cCtr = LBound(myArr, 2) To UBound(myArr, 2)
myArr(rCtr, cCtr) = rCtr * cCtr
Next cCtr
Next rCtr

Set DestCell = Worksheets("Sheet1").Range("a1")

DestCell.Resize(UBound(myArr, 1) - LBound(myArr, 1) + 1, _
UBound(myArr, 2) - LBound(myArr, 2) + 1).Value _
= myArr

End Sub