Loading dates from spreadsheet into array

[Arnold 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 below code 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
'go back to row 2
ActiveCell.Offset([0], [1]).Select 'advance right to next column
of material

Next ' end of looping through the ten columns of materials

End Sub

 

[CBrine]

These code changes seem to resolve the problems.

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

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

Do Until ActiveCell.Value = ""
'on the below code I am getting a run time error 9,
MatType(MatLoopCount).MatDate(DateCount) = ActiveCell
ActiveCell.Offset(1, 0).Select 'advance down to next

DateCount = DateCount + 1
Loop
'go back to row 2
ActiveCell.Offset(0, 1).Select 'advance right to next column of
material

Next ' end of looping through the ten columns of materials

End Sub
HTH
Cal

 

[Arnold Klapheck]

Yes it does work, but can I make MatDate a dynamic array? most of the time
there will be 0 elements but at times it can go up to 30.

 

[CBrine]

How about this? I never actually saw the MatDate() Array exceed the 10
elements, since you seem to reset the row(DateCount) and shift one higher in
the MatType() element. It's now dynamic though, so shouldn't make a
difference.

Public Type Material
MatNum As Integer
MatDate() As Date
End Type
Public MatType(10) As Material
Sub test()
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

Do Until ActiveCell.Value = ""

ReDim MatType(MatLoopCount).MatDate(DateCount + 10)

'on the below code I am getting a run time error 9,
MatType(MatLoopCount).MatDate(DateCount) = ActiveCell
ActiveCell.Offset(1, 0).Select 'advance down to next

'MsgBox MatType(MatLoopCount).MatDate(DateCount)
DateCount = DateCount + 1

Loop
'go back to row 2
Cells(2, ActiveCell.Column + 1).Activate


Next ' end of looping through the ten columns of materials

End Sub

 

[Tom Ogilvy]

Might want to change

ReDim MatType(MatLoopCount).MatDate(DateCount + 10)

to

ReDim Preserve MatType(MatLoopCount).MatDate(1 To DateCount)

so you retain the values you have already stored in the array.