Writing Data to cells

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

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
 
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
 
Thank you.

JMB said:
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
Click to expand...
 
Back
Top