redim preserve the second dimension in a two-dim array

[Guest]

My understanding is in a two dimensional array the second dimension has to be
the same length for each one, What I would like is to have the second
dimension to be different lengths so it only has to be the length to handle
the number of items.
not being able to do that I figure the next best thing is the find the max
number of items in the second dimension and "redim preserve" the second
dimension so I don't have excess number of items. However I am having trouble
doing this getting message that it has already been dimensioned. Anyone have
ideas on how to make this work? Thanks.

below is my code and what I am trying to do.

Public Type Material
MatNum As Integer
MatDate(1 To 30) As Date ' 30 is the max number needed
End Type

Sub Load_Extraction_Dates_Into_Array()

Dim MatLoopCount As Integer, DateCount As Integer, maxDateNum i As Integer

Sheets("Extraction Dates").Select

For MatLoopCount = 1 To 10
With MatType(MatLoopCount)
DateCount = 0
For i = 1 To Cells(Rows.Count, MatLoopCount).End(xlUp).Row - 1
..MatDate(i) = Cells(i + 1, MatLoopCount).Value
DateCount = DateCount + 1
Next
..MatNum = DateCount
if DateCount > maxDateNum then ' add this
maxDateNum = DateCount 'add
end if ' add
End With
Next
redim preserve MatType.MatNum(1 to maxDateNum) 'this dosn't want to work
End Sub

 

[Guest]

Why not create an array of array (also known as a ragged array) something
like this...

Sub test()
Dim ary() As Variant
Dim ary2() As String

ReDim ary(10)
ReDim ary2(5)
ary2(0) = "This"
ary2(1) = "That"
ary(0) = ary2
ReDim ary2(7)
ary2(0) = "The Other"
ary(1) = ary2
End Sub

Each element of ary holds an array of varying size...

 

[Doug Glancy]

Arnold,

In order to use Redim Preserve your original decalaration does not contain
the array size, e.g.:

MatDate() As Date

This means you have to include the Redim Preserve inside your loop.

hth,

Doug

 

[Alan Beban]

Arnold,

In order to use Redim Preserve your original decalaration does not contain
the array size, e.g.:

MatDate() As Date

This means you have to include the Redim Preserve inside your loop.

Doesn't follow.

Dim arr()
ReDim arr(1 To 30)
For i = 1 To 10
arr(i) = i
Next i
Debug.Print UBound(arr) & ", " & arr(5) '<--returns 30, 5
ReDim Preserve arr(1 To 10)
Debug.Print UBound(arr) & ", " & arr(5) '<--returns 10, 5

Alan Beban

 

[Guest]

go back to your orginal thread to see the correction you need.