Paste all values of 2D array straight into worksheet possible?

[ShieBoon]

Hi all, I would like to transfer all my data from a 2D array into a worksheet straight away, without having to use for loops to transfer the data 1 by 1. Is that possible?
Because it seems using the 1 by 1 method is taking really long to load a worksheet.

excel 2007

Thanks
Shie Boon

 

[VoG]

Example

Sub ArrayToRange()
Dim myData(1 To 10, 1 To 2), i As Long
For i = 1 To 10
    myData(i, 1) = i
    myData(i, 2) = i * 10
Next i
Range("A1:B10").Value = myData
End Sub

 

[ShieBoon]

NICE. i'll try that. thanks vog, once again.

 

[ShieBoon]

It worked beautifully. Thank you so much vog.

 

[GTO]

Just to add to Peter's example, in case your array's size may be unknown, you can also use .Resize with UBound and LBound...

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> exa()<br><SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lBnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, ii  <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> myArray() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    x = Evaluate("ROUND(RAND()*30,0)")<br>    y = Evaluate("ROUND(RAND()*30,0)")<br>    lBnd = Evaluate("ROUND(RAND()*1,0)")<br>        <br>    <SPAN style="color:#00007F">ReDim</SPAN> myArray(lBnd <SPAN style="color:#00007F">To</SPAN> x, lBnd <SPAN style="color:#00007F">To</SPAN> y)<br>    <br>    <SPAN style="color:#00007F">For</SPAN> i = lBnd <SPAN style="color:#00007F">To</SPAN> x<br>        <SPAN style="color:#00007F">For</SPAN> ii = lBnd <SPAN style="color:#00007F">To</SPAN> y<br>            myArray(i, ii) = i * ii<br>        <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN><br>    <br>    Range("A1").Resize(UBound(myArray, 1) - <SPAN style="color:#00007F">LBound</SPAN>(myArray, 1) + 1, _<br>                       <SPAN style="color:#00007F">UBound</SPAN>(myArray, 2) - <SPAN style="color:#00007F">LBound</SPAN>(myArray, 2) + 1).Value = myArray<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Mark

 

[kjacw]

Example

Sub ArrayToRange()
Dim myData(1 To 10, 1 To 2), i As Long
For i = 1 To 10
    myData(i, 1) = i
    myData(i, 2) = i * 10
Next i
Range("A1:B10").Value = myData
End Sub

Nice, I like this and it works good and it seems to be what I'm after. 1. What if my data, to fill the array, comes from Sheet3 in 3 columns and 14 rows (A2:C15) and I wanted to populate the Sheet 1 (which is a form) with the data that is in the array in B16. 2. What if my data to fill the array changes i.e. location on Sheet 3 changes (A27:C40) but the same amount of rows and columns and I still wanted to write that data in the same location on Sheet 1(which is a form) B16. How could I accomplish this? I am somewhat a noobie to arrays.