即使值完全匹配，VBA 宏也找不到匹配项

Question

我正在运行一个宏来循环查找基于多个条件的匹配项。我现在想再循环一次，其中(i,13)=“帐户详细信息匹配但付款不同”，但使用列(i,12)作为与(J，14)的匹配项。

当我添加这个循环时:

For i = 1 To UBound(PensionArr)
    match = False
    For J = 1 To UBound(PayrollArr)
        If CStr(PensionArr(i, 13)) = "Account details match but payment differs" Then

            If CStr(PensionArr(i, 12)) = CStr(PayrollArr(J, 14)) And CStr(PensionArr(i, 4)) = CStr(PayrollArr(J, 5)) And CStr(PensionArr(i, 5)) = CStr(PayrollArr(J, 6)) And CStr(PensionArr(i, 8)) = CStr(PayrollArr(J, 9)) Then
                PensionArr(i, 13) = "Complete match"
                Exit For


                'Looks for SC&AC Match but Amount doesn't
            ElseIf CStr(PensionArr(i, 4)) = CStr(PayrollArr(J, 5)) And CStr(PensionArr(i, 5)) = CStr(PayrollArr(J, 6)) And CStr(PensionArr(i, 8)) <> CStr(PayrollArr(J, 9)) Then
                PensionArr(i, 13) = "Account details match but payment differs"


                'Looks Amount Match but SC&AC don't
            ElseIf CStr(PensionArr(i, 4)) <> CStr(PayrollArr(J, 5)) And CStr(PensionArr(i, 5)) <> CStr(PayrollArr(J, 6)) And CStr(PensionArr(i, 8)) = CStr(PayrollArr(J, 9)) Then
                PensionArr(i, 13) = "Account details do not match but payment is correct"

            End If
        End If
    Next J
Next i

它将 (i, 13) 填充为“未找到人员”，这应该是仅当此人不存在于第二张表中时的结果。

如果没有上述循环，它会根据工作表之间不匹配的内容使用 2 个值中的 1 个填充 (i,13)。在我添加上述循环之前，没有“找不到人”。

我的整个代码是:

Sub PensionCheckAccName()

    Application.ScreenUpdating = False
    Application.Calculation =     xlCalculationManual
    Application.EnableEvents = False

    Dim Pension As Worksheet
    Dim Payroll As Worksheet

    Dim cell As Range

    Dim i As Long, J As Long

    Dim PensionArr As Variant
    Dim PayrollArr As Variant

    Dim match As Boolean

    Dim PensionRng As Range
    Dim PayrollRng As Range

    Set Pension = ActiveWorkbook.Sheets("Pensions Bank")
    Set Payroll =  ActiveWorkbook.Sheets("PensionItrent")

    Set PensionRng = Pension.Range("A2",  Pension.Cells(Rows.Count, "A").End(xlUp).Offset(0, 13))
    Set PayrollRng = Payroll.Range("A2", Payroll.Cells(Rows.Count, "A").End(xlUp).Offset(0, 14))

    PensionArr = PensionRng.Value2
    PayrollArr = PayrollRng.Value2

    For i = 1 To UBound(PensionArr)
        match = False
        For J = 1 To UBound(PayrollArr)
            If CStr(PensionArr(i, 6)) = CStr(PayrollArr(J, 7)) And CStr(PensionArr(i, 13)) <> "Complete match" Then


                'Looks for a complete match across SC,AC,Amount

                If CStr(PensionArr(i, 4)) = CStr(PayrollArr(J, 5)) And CStr(PensionArr(i, 5)) = CStr(PayrollArr(J, 6)) And CStr(PensionArr(i, 8)) = CStr(PayrollArr(J, 9)) Then
                    PensionArr(i, 13) = "Complete match"
                    Exit For


                    'Looks for SC&AC Match but Amount doesn't
                ElseIf CStr(PensionArr(i, 4)) = CStr(PayrollArr(J, 5)) And CStr(PensionArr(i, 5)) = CStr(PayrollArr(J, 6)) And CStr(PensionArr(i, 8)) <> CStr(PayrollArr(J, 9)) Then
                    PensionArr(i, 13) = "Account details match but payment differs"


                    'Looks Amount Match but SC&AC don't
                ElseIf CStr(PensionArr(i, 4)) <> CStr(PayrollArr(J, 5)) And CStr(PensionArr(i, 5)) <> CStr(PayrollArr(J, 6)) And CStr(PensionArr(i, 8)) = CStr(PayrollArr(J, 9)) Then
                    PensionArr(i, 13) = "Account details do not match but payment is correct"

                End If
            End If
        Next J
    Next i


    For i = 1 To UBound(PensionArr)
        match = False
        For J = 1 To UBound(PayrollArr)
            If CStr(PensionArr(i, 13)) = "Account details match but payment differs" Then

                If CStr(PensionArr(i, 12)) = CStr(PayrollArr(J, 14)) And CStr(PensionArr(i, 4)) = CStr(PayrollArr(J, 5)) And CStr(PensionArr(i, 5)) = CStr(PayrollArr(J, 6)) And CStr(PensionArr(i, 8)) = CStr(PayrollArr(J, 9)) Then
                    PensionArr(i, 13) = "Complete match"
                    Exit For


                    'Looks for SC&AC Match but Amount doesn't
                ElseIf CStr(PensionArr(i, 4)) = CStr(PayrollArr(J, 5)) And CStr(PensionArr(i, 5)) = CStr(PayrollArr(J, 6)) And CStr(PensionArr(i, 8)) <> CStr(PayrollArr(J, 9)) Then
                    PensionArr(i, 13) = "Account details match but payment differs"


                    'Looks Amount Match but SC&AC don't
                ElseIf CStr(PensionArr(i, 4)) <> CStr(PayrollArr(J, 5)) And CStr(PensionArr(i, 5)) <> CStr(PayrollArr(J, 6)) And CStr(PensionArr(i, 8)) = CStr(PayrollArr(J, 9)) Then
                    PensionArr(i, 13) = "Account details do not match but payment is correct"

                End If
            End If
        Next J
    Next i


    For i = 1 To UBound(PensionArr)
        match = False
        For J = 1 To UBound(PayrollArr)
            If CStr(PensionArr(i, 13)) = "Person not found" Then

                If CStr(PensionArr(i, 12)) = CStr(PayrollArr(J, 14)) And CStr(PensionArr(i, 4)) = CStr(PayrollArr(J, 5)) And CStr(PensionArr(i, 5)) = CStr(PayrollArr(J, 6)) And CStr(PensionArr(i, 8)) = CStr(PayrollArr(J, 9)) Then
                    PensionArr(i, 13) = "Complete match"
                    Exit For


                    'Looks for SC&AC Match but Amount doesn't
                ElseIf CStr(PensionArr(i, 4)) = CStr(PayrollArr(J, 5)) And CStr(PensionArr(i, 5)) = CStr(PayrollArr(J, 6)) And CStr(PensionArr(i, 8)) <> CStr(PayrollArr(J, 9)) Then
                    PensionArr(i, 13) = "Account details match but payment differs"


                    'Looks Amount Match but SC&AC don't
                ElseIf CStr(PensionArr(i, 4)) <> CStr(PayrollArr(J, 5)) And CStr(PensionArr(i, 5)) <> CStr(PayrollArr(J, 6)) And CStr(PensionArr(i, 8)) = CStr(PayrollArr(J, 9)) Then
                    PensionArr(i, 13) = "Account details do not match but payment is correct"
                End If
            End If
        Next J
    Next i

    For i = 1 To UBound(PensionArr)
        If PensionArr(i, 13) = "" Then
            PensionArr(i, 13) = "Person not found"
        End If
    Next i

    PensionRng.Value = PensionArr

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True


End Sub

任何帮助都会很好，因为我正在尝试使用上面的代码来比较基于多个值的每个工作表，但是有很多行，其中 4 列中有 3 列是相同的，并且它们仅在第 4 列中唯一。当我尝试使用第 4 列使行唯一以进行比较(第一个循环)时，它与 136 行不匹配。

我很久没有使用 VBA，所以欢迎任何提示。

如果您需要更多信息，请与我们联系

非常感谢，

马特

Answer 1

问题是我的循环顺序。我在最后设置了“未找到人员”，但试图在设置之前循环使用 (i, 13) 中设置的行。

然后，我将问题顶部的问题循环移到最后，在所有内容都设置好后，它立即起作用。

我只需要重新排序我的循环，让它做我想做的事。

无论如何感谢您的尝试