How to create multidimensional arrays when the size of each is a variable?

[oleppedersen]

Hi, apologies if this has been answered before, I could not find anything after a long search.

I am running through hundreds of data sets, which I am transforming into new data based on a various numbers of dividends and divisors (D&D for short here).

During each process, I count the number of D&D. Then I open the files and want to move them into an array each.

It is easy defining the range and last row as these are onedimensional data - rT(1) will be one range, rT(2) another etc.

However, when I am ready to redim the array that shall hold the data, I run into problems which may be simple for better coders than me:

Normally, I define an array like this (as an example): (I know there are probably other ways to do this, but for me it is a logical way of keeping track of the arrays I use.)

Dim aT() ' I have also dimmed the array this way now

Set rT = ws.Range("A1:B" & LastRow)
ant = rT.Count ' I always count to make the array an exact size
Redim aT(ant)
aT = rT.Value

But in my current work, I am working with that I presume are multidimensional arrays, so the code is:

Dim aT
...
For x = 1 to NumberOfFiles
set rT(x) = ws(x).Range("A1:B" &LastRow(x))
ant = rT(x).Count
redim aT(x, ant) ' Can this be correct???
aT = rT(x).Value ' Can this be correct???

I realize there is something I am not grasping regarding multidimensional arrays.

As I am working on a huge number of files and a number of arrays all the time - and the numbers change - I would love to understand how the easy to use array function could work out here.

Any suggestions are greatly appreciated.

NB: The code is at the moment around 3.500 lines long and contains loads of errors, so the tests I have run up til this point in the code seems pretty inconclusive.

 

[Norie]

What data do you want to hold in the array(s) and how many dimensions do you want/need?

Are you just trying to populate one array with the data from columns A and B of multiple sheets?

 

[oleppedersen]

The data are numerical.

I have anything from 2 to 12 different number of sheets for each occurrence. So I want to populate the same number of arrays with data from a sheet that usually is A1:L500 (but not always so). But basically I want to move the data to an array as it is infinitely faster and the job is very, very huge (hundreds of files are processed in loop).

 

[Norie]

All you would need for that as far as I can see is an array of 2-dimensional arrays.

Each array item in the main array would hold the data for one sheet.

By the way, how exactly are you processing the files?

Are you opening them individually, processing them, closing them and then moving on to the next file?

 

[oleppedersen]

So can I declare the arrays as suggested?

I have a huge master spreadsheet where all the names of the files are listed, and with a code that explains how they will be handled - as this varies. Each line can have up to 10 dividends and 10 divisors, and they will be handled differently. I open up each one and want to transfer them to an array each - but the problem for me is understanding how to refer to the arrays. If there are only one or a few, I always name them differently (like aT(), aR() or aN() to give some examples). But it is clearly not the best way to handle a varying number of arrays - and I really lack understanding of how to write to the arrays when they have the "same" name.

Apologies for lack of technical English, only my 2nd language...