Excel Array Not Repopulating

[Farscape2020]

Good Day,

I am having difficulty with my arrays in vba. In my Excel file, I have 10 worksheets representing the months from September until June. After downloading a csv file and sorting the data (by month and then by day), I add the the range for each month to an array using the following code

        If GetArrLength(attendanceArr) > 0 Then
    
            Erase attendanceArr
            
        End If

        If searchColumn(attendanceSheet, "J", i) = True Then
        
            With attendanceSheet
                startRow = .Range("J1:J" & findLastRow(attendanceSheet, "J")).Find(what:=i, after:=.Range("J1")).row
                endRow = .Range("J1:J" & findLastRow(attendanceSheet, "J")).Find(what:=i, after:=.Range("J1"), searchdirection:=xlPrevious).row
            End With
            
            attendanceArr = Range("A" & startRow & ":" & attendanceSheetLastLetter & endRow)

where "i" is the iterator from 1 to 12 (1 for January, 2 for February, and so on.). Search column is a function that searches column J to determine if "i" (or the month) is listed in the csv file. The findLastRow() is a function to find the last row of the data. When I run the macro, September populates without an issue. However, when it attempts to add the data for October, the data is not added to the array. See Below

Would anyone be able to hazard a guess as to why the array would populate for September (i = 9) and not for October (i =10)? Please let me know if you require any further information.

Thank you for you help.

 

[GWteB]

Without insight what the other tailor made functions are doing (i.e. whether they return the correct result) it's still a guess, but I think the Find method is playing tricks on you.
In your code you don't define all parameters and since the settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use the Find method, the returned result may be different from what you expected.

 

[Farscape2020]

Sorry for the lack of details, it was not intended. Here are all the details.

Function findLastRow(ws As Worksheet, column As String) As Integer 

    findLastRow = ws.Cells(ws.Rows.Count, column).End(xlUp).row

End Function

Function searchColumn(ws As Worksheet, column As String, value As Integer) As Boolean

    Dim rng As Range
    Dim cell As Range
    Dim search As String
    Dim startRow As Integer
    Dim endRow As Integer
    
    Set rng = ws.Range("J2:J" & findLastRow(ws, column))

    Set cell = rng.Find(what:=value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    
    If cell Is Nothing Then
    
        searchColumn = False

    Else
    
         searchColumn = True
         
    End If

End Function

Sub UpdateAttendance()

    Dim attendanceArr As Variant
    Dim i As Integer

    For i = 10 To 10
    
        nameCheck = False
        
        If searchColumn(attendanceSheet, "J", i) = True Then
        
            With attendanceSheet
                startRow = .Range("J1:J" & findLastRow(attendanceSheet, "J")).Find(what:=i, after:=.Range("J1")).row
                endRow = .Range("J1:J" & findLastRow(attendanceSheet, "J")).Find(what:=i, after:=.Range("J1"), searchdirection:=xlPrevious).row
            End With
            
            attendanceArr = Range("A" & startRow & ":" & attendanceSheetLastLetter & endRow)
                
            Select Case i
            
                Case 1
                    
                    populateAttendance ThisWorkbook.Sheets("January"), attendanceArr
                   
                Case 2
                    
                    populateAttendance ThisWorkbook.Sheets("February"), attendanceArr
                    
                Case 3
                    
                    populateAttendance ThisWorkbook.Sheets("March"), attendanceArr
                    
                    
                    populateAttendance ThisWorkbook.Sheets("April"), attendanceArr
                    
                Case 5
                    
                    populateAttendance ThisWorkbook.Sheets("May"), attendanceArr
                    
                Case 6
                    
                    populateAttendance ThisWorkbook.Sheets("June"), attendanceArr
                    
                Case 9
        
                    populateAttendance ThisWorkbook.Sheets("September"), attendanceArr
                    
                Case 10
                    
                    populateAttendance ThisWorkbook.Sheets("October"), attendanceArr
                    
                Case 11
                    
                    populateAttendance ThisWorkbook.Sheets("November"), attendanceArr
                    
                Case 12
                    
                    populateAttendance ThisWorkbook.Sheets("December"), attendanceArr
                   
            End Select
            
        End If
        
    Next i

End Sub

I have tried debugging the code. When I step through the code, it skips the Cases 1 through 8 (January to August) which is what it is supposed to do. When i is 9 (September), it successfully executes the sub populateAttendance. When i is 10 (October), it successfully detects the startRow and endRow (it states the row that 10 begins with is 1302 and ends at row 2211 which is correct). It then executes the attendanceArr line and the result is

The array is empty. However, when I change the For loop to For i = 10 To 10, focus on just October, macro works fine. I do understand I have an error in the macro but I do not understand where.

 

[GWteB]

Since your code is closely related to worksheet data, it would be of great help if you could provide sample data using the XL2BB tool so we're able to reproduce your issue.