Writing Data to cells
 

Hi everyone:

In excel 2003 VBA, I have a rather large 2D array. After doing some sorting
and other things, I am trying to write the data back to the spreadsheet
using loops. However, this writing take a bit too long, and is noticeable.
All my work takes 35 msec, whereas writing it takes 923 msec. Is there a
quick and fast way of dumping an array onto the spreadsheet? Thanks for
your help, and here is my code for writing the data.

'N is the number or rows
'M is the number of columns
c = M + 1
For i = 1 To N
For j = 1 To M
Cells(i, j + c + 1) = AOrig(ind(i), j)
Next j
Next i

Bob

Writing Data to cells
 

Yes - if you have a 2x2 array you could say
Range("A1:B2").Value = YourArray

Or if the array is dynamic you could use something similar to the code
below, although you'd have to make the necessary adjustments if the lower
bounds of your array are something other than 1.


Sub test()
Dim arrTemp(1 To 2, 1 To 2) As Long

arrTemp(1, 1) = 5
arrTemp(1, 2) = 10
arrTemp(2, 1) = 15
arrTemp(2, 2) = 20

Range("A1").Resize(UBound(arrTemp, 2), _
UBound(arrTemp, 1)).Value = arrTemp

End Sub



"Bob" wrote:

Hi everyone:

In excel 2003 VBA, I have a rather large 2D array. After doing some sorting
and other things, I am trying to write the data back to the spreadsheet
using loops. However, this writing take a bit too long, and is noticeable.
All my work takes 35 msec, whereas writing it takes 923 msec. Is there a
quick and fast way of dumping an array onto the spreadsheet? Thanks for
your help, and here is my code for writing the data.

'N is the number or rows
'M is the number of columns
c = M + 1
For i = 1 To N
For j = 1 To M
Cells(i, j + c + 1) = AOrig(ind(i), j)
Next j
Next i

Bob

Writing Data to cells
 

Thank you.

"JMB" wrote in message
...
Yes - if you have a 2x2 array you could say
Range("A1:B2").Value = YourArray

Or if the array is dynamic you could use something similar to the code
below, although you'd have to make the necessary adjustments if the lower
bounds of your array are something other than 1.


Sub test()
Dim arrTemp(1 To 2, 1 To 2) As Long

arrTemp(1, 1) = 5
arrTemp(1, 2) = 10
arrTemp(2, 1) = 15
arrTemp(2, 2) = 20

Range("A1").Resize(UBound(arrTemp, 2), _
UBound(arrTemp, 1)).Value = arrTemp

End Sub



"Bob" wrote:

Hi everyone:

In excel 2003 VBA, I have a rather large 2D array. After doing some
sorting
and other things, I am trying to write the data back to the spreadsheet
using loops. However, this writing take a bit too long, and is
noticeable.
All my work takes 35 msec, whereas writing it takes 923 msec. Is there a
quick and fast way of dumping an array onto the spreadsheet? Thanks for
your help, and here is my code for writing the data.

'N is the number or rows
'M is the number of columns
c = M + 1
For i = 1 To N
For j = 1 To M
Cells(i, j + c + 1) = AOrig(ind(i), j)
Next j
Next i

Bob