Paste cross section of 3D array in one step

[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

 

[Jim Cone]

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

 

[JT]

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

 

[Jim Cone]

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]...

 

[GS]

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

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

 

[JT]

GS

See the third post down in this thread:

https://groups.google.com/group/mic...st&q=paste+array+in+one+step#bc781b8a2f94b4cf

Thanks

JT

 

[GS]

JT explained :

GS

See the third post down in this thread:

https://groups.google.com/group/mic...st&q=paste+array+in+one+step#bc781b8a2f94b4cf

Thanks

JT

Thanks, JT! Close enough to what I'm already using except I use
Resize() to match the target to the size of the array.