G
Guest
I have the following code, which as far as I can tell from the Knowledge
Base, should be correct. However, when it gets to the Redim Preserve
statement I get a Runtime Error 9: Subscript out of range.
Dim rstDROP As ADODB.Recordset
Dim strSQLDROP As String
Set rstDROP = New Recordset
Dim intCountOfDropArray As Integer
Dim sngTotalDropTime As Single
Dim arrDropArray() As Variant
ReDim arrDropArray(1 To 4, 1 To 1)
....
(SQL statement is built in strSQLDROP, file is opened, first set of records
is read into arrDropArray. So far so good...)
(the file contains an unknown number of records, each of which contains an
unknown number of dates. For each record I am finding the number of dates it
contains, the earliest and latest dates in the record, and the "drop date"
(when can we purge this record) which starts out equal to the latest date but
is then added to later in the module based on various criteria. So we have 4
pieces of info for each record.)
....
(We've detected the end of the current record and want to start a new one.
To do this we must increment the last dimension of the array by one...and
this is where the error occurs.)
.MovePrevious 'go back and close out previous record
arrDropArray(2, intCountOfDropArray) = !datBenDate 'nth end date in array
arrDropArray(3, intCountOfDropArray) = sngTotalDropTime 'nth number of dates
arrDropArray(4, intCountOfDropArray) = !datBenDate 'nth drop date
sngTotalDropTime = 0
.MoveNext 'now start entering the new record
ReDim Preserve arrDropArray(4, UBound(arrDropArray, 2) + 1)
intCountOfDropArray = intCountOfDropArray + 1
arrDropArray(1, intCountOfDropArray) = !datBenDate 'nth start date in array
What am I doing wrong?
Base, should be correct. However, when it gets to the Redim Preserve
statement I get a Runtime Error 9: Subscript out of range.
Dim rstDROP As ADODB.Recordset
Dim strSQLDROP As String
Set rstDROP = New Recordset
Dim intCountOfDropArray As Integer
Dim sngTotalDropTime As Single
Dim arrDropArray() As Variant
ReDim arrDropArray(1 To 4, 1 To 1)
....
(SQL statement is built in strSQLDROP, file is opened, first set of records
is read into arrDropArray. So far so good...)
(the file contains an unknown number of records, each of which contains an
unknown number of dates. For each record I am finding the number of dates it
contains, the earliest and latest dates in the record, and the "drop date"
(when can we purge this record) which starts out equal to the latest date but
is then added to later in the module based on various criteria. So we have 4
pieces of info for each record.)
....
(We've detected the end of the current record and want to start a new one.
To do this we must increment the last dimension of the array by one...and
this is where the error occurs.)
.MovePrevious 'go back and close out previous record
arrDropArray(2, intCountOfDropArray) = !datBenDate 'nth end date in array
arrDropArray(3, intCountOfDropArray) = sngTotalDropTime 'nth number of dates
arrDropArray(4, intCountOfDropArray) = !datBenDate 'nth drop date
sngTotalDropTime = 0
.MoveNext 'now start entering the new record
ReDim Preserve arrDropArray(4, UBound(arrDropArray, 2) + 1)
intCountOfDropArray = intCountOfDropArray + 1
arrDropArray(1, intCountOfDropArray) = !datBenDate 'nth start date in array
What am I doing wrong?