ExcelVba陣列不重新填充

很抱歉缺乏資訊。這是更新的帖子。

我在 vba 中使用陣列時遇到困難。在我的 Excel 檔案中，我有 10 個作業表，代表從 9 月到 6 月的月份。下載 csv 檔案并對資料進行排序（按月然后按天）后，我使用以下代碼將每個月的范圍添加到陣列中

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

我試過除錯代碼。當我逐步執行代碼時，它會跳過案例 1 到案例 8（1 月到 8 月），這是它應該做的。當 i 為 9（九月）時，它成功執行了 sub populateAttendance。當 i 為 10（10 月）時，它成功檢測到 startRow 和 endRow（它指出 10 以 1302 開始的行是 2211 行，這是正確的）。然后它執行參加者行，結果是

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.

uj5u.com熱心網友回復：

首先Case 4是失蹤！但請注意，Select Case i您可以使用以下代碼代替整個塊（帶有大量重復代碼）：

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

放置這一行而不是整個Select Case i … End Select塊：

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

注意這里

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

它沒有定義在哪個作業表中Range，因此它可能會選擇錯誤的作業表，如果該作業表是 epmty，則您的陣列為空。使用更具體的東西，例如：

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

始終定義必須在哪個作業表中找到范圍，否則 Excel 有機會選擇錯誤的作業表。

標籤：excel vba

上一篇：如何回圈遍歷Excel行

下一篇：從多個作業表收集Excel資料