excel - 当我通过 for 循环（vba）过滤时输入不匹配

Question

我在循环这个 for 循环时遇到类型不匹配错误。它开始很好，但后来它说类型不匹配。我认为没关系，因为如果单元格不等于“完成”这个短语，它将全部复制到一个新单元格中，所以我认为这不是问题。

20行左右后，在

If MasterList.Cells(i, 32).Value = "Completed" Then

是我得到错误的地方，不知道为什么会发生。前几行工作正常。先感谢您！

Sub BucketReview()
    Dim BucketReport As Variant
    Dim BucketReportWB As Workbook
    Dim CopyLastRow As Long
    Dim ws As Worksheet, wsDest As Worksheet, MasterList As Worksheet
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    'Improves Performance
    BucketReport = Application.GetOpenFilename(Title:="Select your Fee Report", FileFilter:="Excel Files(*.xlsx*),*xlsx*")
    If BucketReport <> False Then Set BucketReportWB = Application.Workbooks.Open(BucketReport) Else Exit Sub

    Set wsDest = Workbooks("Test Fee Deduction Plan Master List.xlsm").Sheets("Paste Reporting Here")
    Set ws = BucketReportWB.Worksheets("Queue Status")
    ws.Unprotect
    
    ws.Columns("A:B").Select
    Selection.Delete Shift:=xlToLeft
    ws.Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    ws.Columns("D:W").Select
    Selection.Delete Shift:=xlToLeft
    
    'paste into A, B , C
    Set wsDest = Workbooks("Test Fee Deduction Plan Master List.xlsm").Sheets("Paste Reporting Here")
    ws.Range("C:E").Copy wsDest.Cells("1", "A")
    ws.Range("A:B").Copy wsDest.Cells("1", "E")
    
    'vlookup on the masterlist
    Set MasterList = Workbooks("Test Fee Deduction Plan Master List.xlsm").Sheets("Master List")
    
    'total row number
    RowCountTotal = MasterList.Cells(Rows.Count, 29).End(xlUp).Row
    
    'Column C
    'FPNameCount = MasterList.Cells(Rows.Count, 3).End(xlUp).Row
    MasterList.Columns(3).ClearContents
    MasterList.Range("C1").Value = "FP Name"
    MasterList.Range("C2").Formula = "=VLOOKUP(F2,'Paste Reporting Here'!A:F,5,FALSE)"
    MasterList.Activate
    MasterList.Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C" & RowCountTotal)
    'copy and paste as values
    MasterList.Range("C2:C" & RowCountTotal).Copy
    MasterList.Range("C2:C" & RowCountTotal).PasteSpecial xlPasteValues
    
    'Column D
    'FPCodeCount = MasterList.Cells(Rows.Count, 4).End(xlUp).Row
    MasterList.Columns(4).ClearContents
    MasterList.Range("D1").Value = "FP RR Code"
    MasterList.Range("D2").Formula = "=VLOOKUP(F2,'Paste Reporting Here'!A:F,6,FALSE)"
    MasterList.Activate
    MasterList.Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D" & RowCountTotal)
    'copy and paste as values
    MasterList.Range("D2:D" & RowCountTotal).Copy
    MasterList.Range("D2:D" & RowCountTotal).PasteSpecial xlPasteValues
    
    'Column AF
    MasterList.Columns(32).ClearContents
    MasterList.Range("AF1").Value = "Reporting Status"
    MasterList.Range("AF2").Formula = "=VLOOKUP(F2,'Paste Reporting Here'!A:C,2,FALSE)"
    MasterList.Activate
    MasterList.Range("AF2").Select
    Selection.AutoFill Destination:=Range("AF2:AF" & RowCountTotal)
    'copy and paste as values
    MasterList.Range("AF2:AF" & RowCountTotal).Copy
    MasterList.Range("AF2:AF" & RowCountTotal).PasteSpecial xlPasteValues
    
    'Column AG
    MasterList.Columns(33).ClearContents
    MasterList.Range("AG1").Value = "Exception Description"
    MasterList.Range("AG2").Formula = "=VLOOKUP(F2,'Paste Reporting Here'!A:C,3,FALSE)"
    MasterList.Activate
    MasterList.Range("AG2").Select
    Selection.AutoFill Destination:=Range("AG2:AG" & RowCountTotal)
    'copy and paste as values
    MasterList.Range("AG2:AG" & RowCountTotal).Copy
    MasterList.Range("AG2:AG" & RowCountTotal).PasteSpecial xlPasteValues
    
    'Exceptions
    
    Dim ActionableItems As Workbook
    Set ActionableItems = Workbooks.Add
    Dim ExceptionRowCount As Long
    
    For i = 2 To RowCountTotal
        
        ExceptionRowCount = ActionableItems.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
        'error at this if statement
        If MasterList.Cells(i, 32).Value = "Completed" Then
        'Do nothing
        Else
            If MasterList.Cells(i, 33).Value = "No Fee Charged for this Month" Then
                'Do nothing
            ElseIf MasterList.Cells(i, 33).Value = "This account will be verified for account closure as no funds are detected in the account. If the account is closed, it will be removed from the Fee Deduction Process. Otherwise, it will run next month as expected." Then
                'Do nothing
            Else
                MasterList.Range(i & ":" & i).Copy ActionableItems.Sheets("Sheet1").Cells(ExceptionRowCount, "A")
            End If
            
        End If
            
    Next i

End Sub

标签： excelvba