loading dates into array problem

[klapheck]

I am loading dates into a dynamic array and am getting error message, "run time error 9, subscript out of range" the dates on the spreadsheet are
formatted as date and look like 2/5/2005 in the formula bar, I tried using DateValue and DateSerial Function, no help. Stepping through the code it
has 2/5/2005 in the activecell.value. Below is my code, any ideas would be appreciated.

Public Type Material
MatNum As Integer
MatDate() As Date
End Type

Public MatType(10) As Material

Sub Load_Extraction_Dates_Into_Array()

Dim MatLoopCount, DateCount As Integer
MatLoopCount = 1

Sheets("Extraction Dates").Select
Range("A2").Select

For MatLoopCount = 1 To 10 'loop right through the ten columns of materials
DateCount = 1 'set/reset date count to beginning
MatType(MatLoopCount).MatNum = DateCount - 1 'start with date count at 0

Do Until ActiveCell.Value = ""
'on the next line I am getting a run time error 9, subscript out of range error message
MatType(MatLoopCount).MatDate(DateCount) = ActiveCell.Value
ActiveCell.Offset([1], [0]).Select 'advance down to next date
DateCount = DateCount + 1
Loop

Cells(1, ActiveCell.Column).Select 'take to row 1
ActiveCell.Offset([1], [1]).Select 'advance right to next column of material

Next ' end of looping through the ten columns of materials

End Sub

[colofnature]

You've declared your array with 10 items in it, but by default they will be numbered 0 through 9, so when your loop tries to access MatType(10) it causes an error. Change the definition to

Public MatType(1 To 10) As Material


Col

[klapheck]

Yes you are correct, I changed it, but I am still getting same error, it is happening when I am trying to put in the very first date.

[colofnature]

Sorry, don't think I've understood the question... it sounds like you have a 10 by 10 grid of dates and you want to read the 10 in each column into an array of dates held in a (type) Material - is that right? If so, change the type declaration to be:

Public Type Material
MatNum As Integer
MatDate(10) As Date
End Type

and it seems to work.

Oh, and btw declaring MatType(10) actually goes from 0 to 10, not 9 - my mistake!


Col

[klapheck]

actually I am doing a two dimentional array with the second dimension being dynamic.

the first dimention is a fixed array of 10, it is made up of a a single integer and a dynamic array of dates.

I have 10 columns of materials and they could have anywhere from 0 to 50 dates in the column, see below (abreviated example of only 3 materials

formatting ruins effect Alumin has three dates, the glass column is blank, Plastic has one )

Alumin | Glass | Plastic
2/1/2004 | | 3/2/2002
2/6/2004 | |
4/7/2004 | |

I want to load the dates into the array but am getting the subscript out of range message.

hope this helps

[colofnature]

So, 10 columns each with varying numbers of rows, first row contains Material name, the rest have dates? This will load each instance of MatType with the column number in MatNum and the dates in MatDate(x) where x is the number of dates.

Please Login or Register  to view this content.

I've added a MatName so you can check which Material the type refers to, and dynamically dimensioned the array of MatType in case you add more than the basic 10.


Col

[klapheck]

This really really helped out, I really can't use the name field because of legacy issues instead I will be using the index number and a lookup table to keep track of names of material. Final question though, can I redimension the second dimension? Dosn't the number of spots reserved in the second dimension have to be the same for all items? I was thinking of keeping track of the max number of items in the second dimension and then redimensioning the second dimension with the redim preserve command but I am getting an error message saying I have alreadey dimensioned this item. Below is what I have so far and the part I would like to add but the redim preserve is giving me error message


Public Type Material
MatNum As Integer
MatDate(1 To 30) As Date ' 30 is the max number need
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

[colofnature]

If you look back at what I posted earlier, when declaring:
Public MatType() As Material

I haven't put in the subscripts, so that inside the routine I can use:
ReDim MatType(1 To Application.WorksheetFunction.CountA([1:1]))

to count the number of material types listed. Similarly, in the type dec, saying:
MatDate() As Date

allows me to use:
ReDim .MatDate(1 To Cells(Rows.Count, MatLoopCount).End(xlUp).Row - 1)

to dynamically size each MatType.MatDate() individually, so that if MatType(1) has 3 dates and MatType(2) has 30, these are the what the upper bounds of each are dimensioned to. So, to find the max number of dates for MatType(n), use:
UBound(MatType(n).MatDate)

Which means you don't need the MatNum at all - I wasn't sure what it was for to be honest, the way I used it it was the same as the index. Including it the code would look like:

Please Login or Register  to view this content.

The rule when declaring an array is, if you want to dynamically resize it, don't put the subscripts in the Dim statement, put them in a ReDim. That way you can resize as often as you like - although in this case I can figure out what size the MatDate() arrays need to be before populating them so I only need to ReDim them once each.


Col