building array

[QB]

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

 

[Rick Rothstein]

It is hard to say what might be wrong as we can't see all of your code. I'm
thinking, though, that the problem may be in CLetter (especially if it is a
function and not an array)... CLetter is being used to convert the column
number to a column letter, right? While this may or may not solve your
problem, you do not need to convert the column number to a letter in your
code... just use the Cell property of the worksheet instead of the Range
property to make your references. Assuming these line do what you want them
to...

aNewDbData(0, iarrPosn) = _
Range(CLetter(iCounter) & "2").Value 'Date
aNewDbData(1, iarrPosn) = _
Range(CLetter(iCounter) & "10").Value 'No Hrs Atl Dispo

just change them to these...

aNewDbData(0, iarrPosn) = Cells(2, iCounter).Value 'Date
aNewDbData(1, iarrPosn) = Cells(10, iCounter.Value 'No Hrs Atl Dispo

As I said, I'm not sure if this addresses your problem or not; but, for your
particular case, the Cells reference method has the advantage of being
easier to read than the method you posted.

 

[Dave Peterson]

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]

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

QB