How to write an array to a range without looping
 

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

How to write an array to a range without looping
 

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


--
isabelle

Le 2011-04-26 08:18, Andy a écrit :
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

How to write an array to a range without looping
 

On Apr 26, 5:18*am, Andy wrote:
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.

How to write an array to a range without looping
 

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



On 04/26/2011 07:18, Andy wrote:
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

--
Dave Peterson