Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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! |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. Dave wrote: 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 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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] Dave wrote: 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 |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 Dave wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How do I Preserve Borders? | Excel Discussion (Misc queries) | |||
Preserve settings | Excel Discussion (Misc queries) | |||
Simplify syntax of a Sum(IF... Array | Excel Worksheet Functions | |||
syntax to refer to a range as an array? | Excel Worksheet Functions | |||
Want to Use an Array; Not Sure of Syntax | Excel Discussion (Misc queries) |