View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
[email protected][_2_] gimme_this_gimme_that@yahoo.com[_2_] is offline
external usenet poster
 
Posts: 236
Default Populating a multi dimensional array

This might help. Note the sweet ending which allows you to insert the
array into another worksheet in a single VBA statement.

Sub move()

Dim arr() As Variant
Dim i As Long
Dim b As Workbook
Dim s1 As Worksheet
Dim s2 As Worksheet
Set b = ThisWorkbook
Set s1 = b.Sheets("Sheet1")
Set s2 = b.Sheets("Sheet2")
For i = 1 To 122
ReDim Preserve arr(1 To i)
arr(i) = Array(s1.Cells(i, 1), s1.Cells(i, 2), s1.Cells(i, 3))
Next
s2.Range("A1:C122").Value =
Application.Transpose(Application.Transpose(arr))
End Sub