VBA syntax help: ReDim Preserve an array

[Dave]

I'd like to use a multi-dimensional array to clean up some data in a
spreadsheet: each row contains data pertaining to 5 years of annual
data spread out over many columns, and I'd like to create a normalized
data table out of it. I thought I would write information to a
dimensional array, insert a new tab, and write data from the array
into the new tab.

The only twist is: a row may or may not contain data for all 5 years,
so I thought I would ReDim Preserve the array and augment the row
count by 1 when data is present. What I have is

ReDim arrData(1 To Z, 1 To 13) As String 'declaration; unknown # rows,
13 columns

for K = 1 to LastRow 'LastRow predefined in the code as the last row
of the tab
if [certain cell value for year 1] > 0 then
Z = Z + 1
ReDim Preserve arrData (1 to Z), 1 to 13)
arrData(z,1) = [cell value]
etc
end if

if [certain cell value for year 2] > 0 then
Z = Z + 1
ReDim Preserve arrData (1 to Z), 1 to 13)
arrData(z,1) = [cell value]
etc
end if

etc
etc
Next K

....and row 1 of the array populates, but I get a runtime error 9,
Subscript Out Of Range error in the Year 2 section of the code, and I
can't seem to debug it.

Can anyone suggest a fix?

Thanks!

 

[Dave Peterson]

First, it's better to copy|paste from your real code.

It'll avoid syntax errors like:
ReDim Preserve arrData (1 to Z), 1 to 13)

And second, look at ReDim in VBA's help. You'll find that you can only change
the last dimension.

I'd like to use a multi-dimensional array to clean up some data in a
spreadsheet: each row contains data pertaining to 5 years of annual
data spread out over many columns, and I'd like to create a normalized
data table out of it. I thought I would write information to a
dimensional array, insert a new tab, and write data from the array
into the new tab.

The only twist is: a row may or may not contain data for all 5 years,
so I thought I would ReDim Preserve the array and augment the row
count by 1 when data is present. What I have is

ReDim arrData(1 To Z, 1 To 13) As String 'declaration; unknown # rows,
13 columns

for K = 1 to LastRow 'LastRow predefined in the code as the last row
of the tab
if [certain cell value for year 1] > 0 then
Z = Z + 1
ReDim Preserve arrData (1 to Z), 1 to 13)
arrData(z,1) = [cell value]
etc
end if

if [certain cell value for year 2] > 0 then
Z = Z + 1
ReDim Preserve arrData (1 to Z), 1 to 13)
arrData(z,1) = [cell value]
etc
end if

etc
etc
Next K

...and row 1 of the array populates, but I get a runtime error 9,
Subscript Out Of Range error in the Year 2 section of the code, and I
can't seem to debug it.

Can anyone suggest a fix?

Thanks!

 

[Dave]

And second, look at ReDim in VBA's help. You'll find that you can only change

the last dimension.

Thanks, Dave. Can you recommend a dynamic way create a N Rows, Y
Columns array where N is uncertain?

 

[Dave Peterson]

Just reverse your rows and columns--both when you assign the values and when you
read the values back.

ReDim Preserve arrData (1 to 13, 1 to z)
arrData(1,z) = [cell value]

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

ResizeArray myArray, N, Y

Alan Beban