Thank you all for your insight! I have learnt several new things and have
resolved the problem as well!!!

QB





"Dave Peterson" wrote:

Unless you're doing something special (like "Option base 1" at the top of your
module), this line:

ReDim Preserve aNewDbData(1, iNoWks) As Variant
is the equivalent of:
ReDim Preserve aNewDbData(0 to 1, 0 to iNoWks) As Variant

And counting from 0 to 73 gives 74 entries.

I'd use:
ReDim Preserve aNewDbData(0 to 1, 0 to iNoWks - 1) As Variant
(I like being explicit in my bounds.)

And instead of using Cletter(), you can use cells() (and I qualified my ranges
to the activesheet, too):

Option Explicit
Sub testme01()
Dim iStartWkCol As Long
Dim iEndWkCol As Long
Dim aNewDBData() As Variant
Dim iNoWks As Long
Dim iCounter As Long
Dim iArrPosn As Long
Dim wks As Worksheet

Set wks = ActiveSheet

iStartWkCol = 2
iEndWkCol = 74
iNoWks = iEndWkCol - iStartWkCol + 1

'Resize our array to accomodate the data
ReDim Preserve aNewDBData(0 To 1, 0 To iNoWks - 1) As Variant

With wks
'Loop through the Week Cols of the Worksheet
For iCounter = iStartWkCol To iEndWkCol
'Date
iArrPosn = iCounter - iStartWkCol
aNewDBData(0, iArrPosn) = .Cells(2, iCounter).Value

'No Hrs Atl Dispo
aNewDBData(1, iArrPosn) = .Cells(10, iCounter).Value
Next iCounter
End With
End Sub

But there is no reason why you couldn't use different limits:

ReDim Preserve aNewDBData(0 To 1, iStartWkCol To iEndWkCol) As Variant

As in:

Option Explicit
Sub testme01()
Dim iStartWkCol As Long
Dim iEndWkCol As Long
Dim aNewDBData() As Variant
Dim iNoWks As Long
Dim iCounter As Long
Dim iArrPosn As Long
Dim wks As Worksheet

Set wks = ActiveSheet

iStartWkCol = 2
iEndWkCol = 74
iNoWks = iEndWkCol - iStartWkCol + 1

'Resize our array to accomodate the data
ReDim Preserve aNewDBData(0 To 1, iStartWkCol To iEndWkCol) As Variant

With wks
'Loop through the Week Cols of the Worksheet
For iCounter = iStartWkCol To iEndWkCol
'Date
aNewDBData(0, iCounter) = .Cells(2, iCounter).Value

'No Hrs Atl Dispo
aNewDBData(1, iCounter) = .Cells(10, iCounter).Value
Next iCounter
End With
End Sub

I find that this kind of thing makes life easier when debugging. The counter
represents the column number and doesn't need to be adjusted.

To loop through the array, though, I'd use something like:

For iCounter = LBound(aNewDBData, 2) To UBound(aNewDBData, 2)
MsgBox aNewDBData(0, iCounter) & vbLf & aNewDBData(1, iCounter)
Next iCounter

So I'm looping through the elements in the 2nd dimension of that array.

========
In fact, after I've redimmed that array, I could use it in the initial loop:

Option Explicit
Sub testme01()
Dim iStartWkCol As Long
Dim iEndWkCol As Long
Dim aNewDBData() As Variant
Dim iNoWks As Long
Dim iCounter As Long
Dim iArrPosn As Long
Dim wks As Worksheet

Set wks = ActiveSheet

iStartWkCol = 2
iEndWkCol = 74
iNoWks = iEndWkCol - iStartWkCol + 1

'Resize our array to accomodate the data
ReDim Preserve aNewDBData(0 To 1, iStartWkCol To iEndWkCol) As Variant

With wks
'Loop through the Week Cols of the Worksheet
For iCounter = LBound(aNewDBData, 2) To UBound(aNewDBData, 2)
'Date
aNewDBData(0, iCounter) = .Cells(2, iCounter).Value

'No Hrs Atl Dispo
aNewDBData(1, iCounter) = .Cells(10, iCounter).Value
Next iCounter
End With

For iCounter = LBound(aNewDBData, 2) To UBound(aNewDBData, 2)
MsgBox aNewDBData(0, iCounter) & vbLf & aNewDBData(1, iCounter)
Next iCounter

End Sub


QB wrote:

I am trying to build a dynamic array. Sadly, I can't seem to see my error.
I have


iStartWkCol = 2
iEndWkCol = 74
iNoWks = iEndWkCol - iStartWkCol + 1
ReDim Preserve aNewDbData(1, iNoWks) As Variant 'Resize our array to
accomodate the data

For iCounter = iStartWkCol To iEndWkCol 'Loop through the
Week Cols of the Worksheet
iarrPosn = iCounter - iStartWkCol
aNewDbData(0, iarrPosn) = _
Range(CLetter(iCounter) & "2").Value 'Date
aNewDbData(1, iarrPosn) = _
Range(CLetter(iCounter) & "10").Value 'No Hrs Atl Dispo
Next iCounter

My problem is that iNoWks, thus my array size is correctly giving 73, but
when it performs the loop it always run to iCounter = 42 and stops? Does
anyone see what I am doing wrong here?

Thank you for the extra set of eyes!

QB

--

Dave Peterson
.