Sorry for the lack of information. Here is the updated post.

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

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.

Thank you for you help.

CodePudding user response：

First of all Case 4 is missing! But note that instead of the entire Select Case i block (with a lot of repeating code) you can just use the following:

' define this before your for loop starts
Dim MonthsList As Variant
MonthsList = Array("January", "February", "March", "April", "May", "June", "September", "October", "November", "December")

Put this line instead of the entire Select Case i … End Select block:

populateAttendance ThisWorkbook.Sheets(MonthsList(i - 1)), attendanceArr

Note that here

attendanceArr = Range("A" & startRow & ":" & attendanceSheetLastLetter & endRow)

it is not defined in which sheet that Range is, therefore it might pick the wrong sheet and if this sheet is epmty your array is empty. Use something more specific like:

attendanceArr = attendanceSheet.Range("A" & startRow & ":" & attendanceSheetLastLetter & endRow).Value

Always define in which sheet a range has to be found or Excel has the chance to pick the wrong sheet.