VBA实现Pay ID与Premium双列匹配校验问题求助

表格样例：Our_Data区域和Customer_Data区域存在相同Pay ID但不同Premium的记录，如同一Pay ID对应2.70和12.35两种Premium。

我需要编写VBA代码，校验Pay ID是否同时存在于Our_Data和Customer_Data区域，且该Pay ID对应的所有Premium也在两个区域中匹配。

目前尝试的代码仅识别Pay ID，无法区分同一Pay ID下的2.70 Premium和12.35 Premium。期望输出：

• 若两个区域存在相同的Pay ID+Premium组合，Match列对应行标Y
• 若Pay ID/Premium仅存在于某一区域，则对应标N或相关提示

以下是我编写的代码，它能正确为两个12.35 Premium标记Y，但却为两个2.70 Premium标记N，而实际上它们在两个区域都存在。恳请帮忙修正。

Sub payIDRecon1()
    Dim eRow1 As Long, eRow2 As Long
    Dim cell1 As Range, cell2 As Range
    Dim rngOurData As Range, rngCustData As Range
    Dim data As Worksheet
    Dim noMatch As Worksheet
    Dim payID1 As Variant, payID2 As Variant
    Dim pay_id_row As Long
    pay_id_row = 3

    Set data = ThisWorkbook.Sheets("Data")
    Set noMatch = ThisWorkbook.Sheets("No_Match_Pay_ID")

    ' Find the last row in columns A and G
    eRow1 = data.Cells(data.Rows.Count, 1).End(xlUp).row
    eRow2 = data.Cells(data.Rows.Count, 7).End(xlUp).row

    ' Set the ranges for our data and customer data
    Set rngOurData = data.Range("A3:A" & eRow1)
    Set rngCustData = data.Range("G3:G" & eRow2)

    ' Loop through each cell in rngOurData
    For Each cell1 In rngOurData
        payID1 = cell1.Value
        
        ' Reset flag for each iteration
        Dim foundInCustData As Boolean
        foundInCustData = False
        
        ' Loop through each cell in rngCustData
        For Each cell2 In rngCustData
            payID2 = cell2.Value
            
            ' Check if payID1 exists in rngCustData
            If payID1 = payID2 Then
                foundInCustData = True
                
                ' Check if corresponding premium matches
                If cell1.Offset(0, 4).Value = cell2.Offset(0, 3).Value Then
                    cell1.Offset(0, 5).Value = "Y"
                Else
                    cell1.Offset(0, 5).Value = "N"
                    
                    With noMatch
                    
                        .Range("A" & pay_id_row).Value = data.Cells(cell1.row, 1).Value
                        .Range("B" & pay_id_row).Value = data.Cells(cell1.row, 2).Value
                        .Range("C" & pay_id_row).Value = data.Cells(cell1.row, 3).Value
                        .Range("D" & pay_id_row).Value = data.Cells(cell1.row, 4).Value
                        .Range("E" & pay_id_row).Value = data.Cells(cell1.row, 5).Value
                        pay_id_row = pay_id_row + 1
                        
                    End With
                    
                End If
                
                Exit For ' Exit the inner loop once a match is found
                
            End If
            
        Next cell2
        
        ' If payID1 not found in rngCustData, mark it as such
        If Not foundInCustData Then
            cell1.Offset(0, 5).Value = "Not Found in Customer Data"
            
            With noMatch
            
                .Range("A" & pay_id_row).Value = data.Cells(cell1.row, 1).Value
                .Range("B" & pay_id_row).Value = data.Cells(cell1.row, 2).Value
                .Range("C" & pay_id_row).Value = data.Cells(cell1.row, 3).Value
                .Range("D" & pay_id_row).Value = data.Cells(cell1.row, 4).Value
                .Range("E" & pay_id_row).Value = data.Cells(cell1.row, 5).Value
                pay_id_row = pay_id_row + 1
            
            End With
            
        End If
        
    Next cell1
    
    ' Reset rngCustData for the second loop
    Set rngCustData = data.Range("G3:G" & eRow2)
    
    pay_id_row = 3

    ' Loop through each cell in rngCustData
    For Each cell2 In rngCustData
        payID2 = cell2.Value
        
        ' Reset flag for each iteration
        Dim foundInOurData As Boolean
        foundInOurData = False
        
        ' Loop through each cell in rngOurData
        For Each cell1 In rngOurData
            payID1 = cell1.Value
            
            ' Check if payID2 exists in rngOurData
            If payID1 = payID2 Then
                foundInOurData = True
                
                ' Check if corresponding premium matches
                If cell2.Offset(0, 3).Value = cell1.Offset(0, 4).Value Then
                    cell2.Offset(0, 4).Value = "Y"
                Else
                    cell2.Offset(0, 4).Value = "N"
                    
                    With noMatch
            
                        .Range("I" & pay_id_row).Value = data.Cells(cell2.row, 7).Value
                        .Range("J" & pay_id_row).Value = data.Cells(cell2.row, 8).Value
                        .Range("K" & pay_id_row).Value = data.Cells(cell2.row, 9).Value
                        .Range("L" & pay_id_row).Value = data.Cells(cell2.row, 10).Value
                        pay_id_row = pay_id_row + 1
            
                    End With
                    
                End If
                
                Exit For ' Exit the inner loop once a match is found
                
            End If
            
        Next cell1
        
        ' If payID2 not found in rngOurData, mark it as such
        If Not foundInOurData Then
            cell2.Offset(0, 4).Value = "Not Found in Our Data"
            
            With noMatch
            
                .Range("I" & pay_id_row).Value = data.Cells(cell2.row, 7).Value
                .Range("J" & pay_id_row).Value = data.Cells(cell2.row, 8).Value
                .Range("K" & pay_id_row).Value = data.Cells(cell2.row, 9).Value
                .Range("L" & pay_id_row).Value = data.Cells(cell2.row, 10).Value
                pay_id_row = pay_id_row + 1
            
            End With
            
        End If
        
    Next cell2
    
End Sub 

内容的提问来源于stack exchange，提问作者Darryl Hewitt