Paste cross section of 3D array in one step

  • Thread starter Thread starter JT
  • Start date Start date
J

JT

Hello team!

I wonder if anybody could help me with the following problem:

I have a macro which creates a 5x52 array of data for each year in a
50 year period. This gives me a 5x52x50 array overall which has been
named "rngArray1".

I need some code to paste one year's worth of data into excel in one
go. At the moment I have the following:

*******************************
intYear = 1 '(To paste the data for year 1 in my timeline)
For j = 1 To 5
For k = 1 To 52
Sheets("By Cohort").Cells(j, k) = rngArray1(j, k, intYear)
Next j
Next i
*******************************

My problem is that looping through and pasting one element of the data
at a time like this takes a long time, and is prohibitively slow when
I need to repeat this process hundreds of times.

If my array was 2D then I would find it very easy to drop the data
into excel in one step without having to use a loop. However, I'm
having trouble doing this with a 3D array. I can't seem to find a way
of pasting only the desired cross section in one go.

I hope this is clear. I'd really appreciate any help so get in touch
if you'd like me clarify anything.

Thanks

John
 
Use an additional array that has two dimensions.
Use your loop to transfer the data for a single year to the new array.
Then add the new array data to the worksheet.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Formats & Styles: lists/removes unused styles & number formats - in the free folder)




"JT" <[email protected]>
wrote in message
news:befc484d-2888-48f9-9c39-1384f56730e3@a28g2000prb.googlegroups.com...
 
Thanks Jim

I'd expect this to speed things up quite a bit so I'll give it a go.

I am surprised however that there isn't a way of doing this without
using a loop. Any more ideas out there?

Thanks

John
 
I think you will find the speed acceptable.
As an alternative you could use a Dictionary to contain the arrays.
'---
Sub StoreAndRetrieveArrays()
'Jim Cone - Portland, Oregon USA - February 2011
Dim oDic As Object
Dim i As Long
Dim M As Long
Dim N As Long
Dim arrTemp(1 To 5, 1 To 52) As Variant

'A Dictionary can hold/contain anything.
Set oDic = CreateObject("Scripting.Dictionary")
oDic.CompareMode = 0 'binary

'Create 50 separate 2 dimensional arrays
For M = 1 To 50 'year
For i = 1 To 5 'day
For N = 1 To 52 'week
arrTemp(i, N) = "Day " & i & ", Week " & N & ", Year " & M
Next 'n
Next 'i

'Add each array to the Dictionary using year as the Key
oDic.Add M, arrTemp()
Next 'M

'Add the first and last years to the worksheet - no loop required
Range("A1:AZ5").Value = oDic.Item(1)
Range("A10:AZ14").Value = oDic.Item(50)

Set oDic = Nothing
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Extras for Excel add-in: convenience built-in)




"JT" <[email protected]>
wrote in message
news:[email protected]...
 
JT expressed precisely :
If my array was 2D then I would find it very easy to drop the data
into excel in one step without having to use a loop
Click to expand...

Can you demonstrate (via example code) how to do this?
 
Back
Top