Dynamic Array

I need a 2-dimensinal dynamic array, but the problem is, that I don't have the 1st dimension as fixed number. It is read from Named Range instead. It doesnt change after the array is declared for the scope of procedure, but it may be initialized at start, when according cell is empty.

I have a sample of data, where an indetermined number of articles are delivered at certain dates (weekly or monthly). The maximal possible number of different weeks is stored in worksheet cell, which is defined as Named Range, p.e. MaxWeeks. I need to read those data into array p.e. arrData(1 To MaxWeeks+2, 1 To n), where arrData(1, n) is quantity of article n for 1st week, ..., arrData(MaxWeeks, n) is quantity of article n for last week, arrData(MaxWeeks+1, n) is article number, and arrData(MaxWeeks+2, n) is total quantity of article n.

The idea is to declare the dynamic array, and the use Redim Preserve to add new row for every new article. But I haven't found a way to make VBA to accept MaxWeeks as a constant (and because I may need to initialize MaxWeeks, I can't declare it in declarations section anyway), and I can't use Redim to add a new dimension.

Is there some solution?
Thanks in advance!

Arvi Laanemets

Redim Preserve only preserves the last dimension of an array - i.e. n rather than MaxWeeks.

Originally Posted by StephenR

Redim Preserve only preserves the last dimension of an array - i.e. n rather than MaxWeeks.

I know!

Originally Posted by MarvinP

It looks to me you want a Dynamic Named Range.

I use various dynamic ranges frequently, but they aren't applicaple for current task.
I have 4 different source tables in separate files (either Excel workbooks or text files) - everyone with different data structure, with different delivery frequency and with different delivery scope from couple of months up to year. And the result will be a text file applicable for data import into 3rd party program. The Excel workbook, where the VBA procedure is started, acts as go-between - it reads data from source tables, makes all necessary calculations, and writes results into text file - without writing a single bit of data into some of its own worksheets.