Arrays to Column Vectors

[Petro]

Dear All
I am in need of a macro, which is able to transform two 64x64 dataarrays on each worksheet into columns of 4096 datapoints each in such a way that the order of cells is kept equal across files. I do additionally need to apply this macro to >3500 files - Can anyone help? Please note that the second array per worksheet is always the transposed first array - Maybe this reduces the programming effort? Here is a (size-reduced) example of what I need

Example of the problem: Two small starting arrays:
1 2 3
4 5 6
7 8 9

1 4 7
2 5 8
3 6 9


The type of transformation I need:
Normal Transposed
1 1
4 2
7 3
2 4
5 5
8 6
3 7
6 8
9 9

Problems I hit were related to a) The macro being much too long (and hence wouldnt run) and b) My unability to make this macro run automatically on all files in a folder (in my case these are many). I am quite sure I owe this problems solely to my own clumsiness, so - please enlighten me!
Thank you very much in advance for your time and suggestions!

Edit: Sorry for crossposting, pls delete from the "worksheet functions thread"

[Leith Ross]

Hello Petro,

This macro searches the given folder for all Excel workbook files (*.xls) and opens each one. It will transform two 2-dimensional arrays represented by ranges on the worksheet into a single 2-dimensional array elsewhere on the same sheet. You will need to change some things in the macro before you run it: the name of the worksheet in the workbook, the matix ranges, and the destination range of the transform. These are marked in red. You only need specify the location of first cell for the destination.

Please Login or Register  to view this content.

[Petro]

Hi Leith,
this is truely awesome!!! Thanks a million for your efforts! I will give this a try and see how it comes through... I will post back results and the final macro code...
More soon!
-Petro