VBA Trying to write new array from another array

[AliCat]

I'm trying to create a new array from a larger array that has 4087 rows and 19 columns. I cycle through the main array to count the number of records matching the year 2017 of which there are 714. So my new array will be 714 rows by 11 columns. Most of the undernoted code works including the ReDim but falls down when I try to move the data from the main array (cb) to the new one (cb2). Is there something I'm missing here. Can someone point me in the right direction. Thanks.

Sub testArray()
Dim r As Long, c As Long, n As Long
Dim cb()
Dim cb2()
cb = Range("cbdata")    ' 4087 rows  19 columns
lb1 = LBound(cb, 1)     ' lr
ub1 = UBound(cb, 1)     ' ur
lb2 = LBound(cb, 2)     ' lc
ub2 = UBound(cb, 2)     ' uc

n = 0: p = 0
' clear immediate window
    For i = 0 To 200
        Debug.Print
    Next i

' get number of records relating to 2017 year - held in column 17
    For r = 1 To ub1  ' rows
        p = p + 1
        'For c = 1 To ub2  ' columns
        If cb(r, 17) = 2017 Then
        Debug.Print "cb ", cb(r, 1), cb(r, 2), cb(r, 7), cb(r, 8)    ' reports data ok
        n = n + 1
        End If
        'Next c
    Next r
' above code works ok

' move only 2017 (year) records to new array
    ReDim cb2(1 To n, 1 To 11)  ' n = 714
' msgbox lbound(cb2,1) & cr & ubound(cb2,1) & cr & lbound(cb2,2) & cr & ubound(cb2,2)
' msgbox reports - 1, 714, 1, 11   so  redim is working.
    n = 0
    For r = 1 To ub1  ' rows
        If cb(r, 17) = 2017 Then
        n = n + 1
        cb2(n, 1) = cb(r, 1)
        cb2(n, 2) = cb(r, 2)
        cb2(n, 3) = cb(r, 3)
        cb2(n, 4) = cb(r, 4)
        cb2(n, 5) = cb(r, 5)
        cb2(n, 6) = cb(r, 6)
        If cb(r, 8) < 0 Then cb2(n, 7) = "Cr" Else cb2(n, 7) = "Dr"
        cb2(n, 8) = cb(r, 8)
        cb2(n, 9) = cb(r, 9)
        cb2(n, 10) = ":"
        cb2(n, 11) = "CB"
        End If
    Debug.Print
    Debug.Print "cb ", cb(r, 1), cb(r, 2), cb(r, 7), cb(r, 8)
    'Debug.Print "cb2 ", cb2(n, 1), cb2(n, 2), cb2(n, 7), cb2(n, 8)
        ' subscript out of range with above line uncommented
        ' no data seems to get written cb2
    Next r
Debug.Print
Debug.Print "Rows:", lb1, ub1
Debug.Print "Cols:", lb2, ub2
Debug.Print "Recs:", p, n
End Sub

 

[Eric W]

If your first row of cb doesn't have 2017, then your debug line will fail with subscript out of bounds, because n will still be 0, and you set the lower bound of cb2 to 1.

 

[Fluff]

You need to put the debug.print lines inside the If statement.
Otherwise if you hit that line before anything is added to the array n will be 0 which is causing the error.

 

[AliCat]

Grateful thanks to you both. All working fine now. I was relying too much on the debug info rather that testing the output after the process.

 

[Fluff]

Glad we could help & thanks for the feedback