在VBA的For Next循环中满足多IF条件时获取第2、3、4次匹配值

各位好，我并非资深VBA开发者，只是偶尔通过自动化提升工作效率。我有两个工作表的表格，需要在满足多个条件时，将T2的数据填充到T1中。

表格示例 #

T1工作表（M表） #

T2工作表（S表） #

当前代码 #

我已成功填充前两列，但无法获取第n次匹配的值。相关代码如下：

Sub test()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim a As String
Dim b As String
Dim c As String
Dim f As String

    Set sht1 = ThisWorkbook.Worksheets("M")
    Set sht2 = ThisWorkbook.Worksheets("S")

For d = 4 To sht1.UsedRange.Rows.count
For e = 3 To sht2.UsedRange.Rows.count
For g = 5 To sht1.UsedRange.Columns.count

    a = sht1.Cells(d, 2).Value
    b = sht1.Cells(2, g).Value
    c = sht1.Cells(d, 3).Value
    f = sht1.Cells(d, 4).Value

If sht2.Cells(e, 2).Value = a _
    And sht2.Cells(e, 7).Value = b _
        And (sht2.Cells(e, 3).Value = c _
            Or sht2.Cells(e, 4).Value = c _
            Or sht2.Cells(e, 5).Value = c _
            Or sht2.Cells(e, 6).Value = c) _
        And sht2.Cells(e, 8).Value = "All" _
        And sht2.Cells(e, 8).Value <> "" _
    Then

    sht1.Cells(d, g).Value = sht2.Cells(e, 9).Value
    sht1.Cells(d, g + 1).Value = sht2.Cells(e, 10).Value

End If

If sht2.Cells(e, 2).Value = a _
    And b = "L1" _
        And (sht2.Cells(e, 3).Value = c _
            Or sht2.Cells(e, 4).Value = c _
            Or sht2.Cells(e, 5).Value = c _
            Or sht2.Cells(e, 6).Value = c) _
        And sht2.Cells(e, 8).Value = f _
        And sht2.Cells(e, 8).Value <> "" _
    Then

    sht1.Cells(d, g).Value = sht2.Cells(e, 9).Value
    sht1.Cells(d, g + 1).Value = sht2.Cells(e, 10).Value

End If

  If sht2.Cells(e, 2).Value = a _
    And b = "L2" _
        And (sht2.Cells(e, 3).Value = c _
            Or sht2.Cells(e, 4).Value = c _
            Or sht2.Cells(e, 5).Value = c _
            Or sht2.Cells(e, 6).Value = c) _
        And sht2.Cells(e, 8).Value = f _
        And sht2.Cells(e, 8).Value <> "" _
    Then

    sht1.Cells(d, g).Value = sht2.Cells(e, 9).Value
    sht1.Cells(d, g + 1).Value = sht2.Cells(e, 10).Value

End If

  Next g
    Next e
        Next d

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

你的代码每次匹配到符合条件的行时会直接覆盖之前的值，所以只能保留最后一次匹配结果。要获取第n次匹配的值，需要添加计数器跟踪匹配次数，同时根据目标列的序号（1或2）确定填充对应匹配结果。

修改后的代码 #

Sub test()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim a As String, b As String, c As String, f As String
Dim matchCount As Integer ' 记录当前行的匹配次数
Dim targetColNum As Integer ' 当前目标列对应的序号（1或2）

    Set sht1 = ThisWorkbook.Worksheets("M")
    Set sht2 = ThisWorkbook.Worksheets("S")

' 遍历T1的每一行数据
For d = 4 To sht1.UsedRange.Rows.Count
    a = sht1.Cells(d, 2).Value
    c = sht1.Cells(d, 3).Value
    f = sht1.Cells(d, 4).Value
    
    ' 遍历T1的每一个目标列
    For g = 5 To sht1.UsedRange.Columns.Count
        b = sht1.Cells(2, g).Value
        targetColNum = sht1.Cells(3, g).Value ' 获取当前列是1还是2
        matchCount = 0 ' 重置匹配计数器
        
        ' 遍历T2的每一行寻找匹配
        For e = 3 To sht2.UsedRange.Rows.Count
            Dim isMatch As Boolean
            isMatch = False
            
            ' 基础匹配条件：A列相同，且B1-B4中有一个等于C值
            If sht2.Cells(e, 2).Value = a Then
                If sht2.Cells(e, 3).Value = c Or sht2.Cells(e, 4).Value = c _
                    Or sht2.Cells(e, 5).Value = c Or sht2.Cells(e, 6).Value = c Then
                    
                    ' 分场景判断额外条件
                    Select Case b
                        Case "Test"
                            If sht2.Cells(e, 7).Value = b And sht2.Cells(e, 8).Value = "All" And sht2.Cells(e, 8).Value <> "" Then
                                isMatch = True
                            End If
                        Case "L1", "L2"
                            If sht2.Cells(e, 8).Value = f And sht2.Cells(e, 8).Value <> "" Then
                                isMatch = True
                            End If
                    End Select
                End If
            End If
            
            ' 匹配成功则计数器加1，等于目标序号时填充值并退出循环
            If isMatch Then
                matchCount = matchCount + 1
                If matchCount = targetColNum Then
                    sht1.Cells(d, g).Value = sht2.Cells(e, 9).Value
                    sht1.Cells(d, g + 1).Value = sht2.Cells(e, 10).Value
                    Exit For ' 找到目标匹配后停止当前列的遍历，避免覆盖
                End If
            End If
        Next e
    Next g
Next d

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

代码说明 #

1. 匹配计数器：用matchCount记录当前列下找到的匹配次数，每次符合条件就递增。
2. 目标序号识别：通过targetColNum读取当前列对应的是1还是2（比如Test列下的1或2），当匹配次数等于该序号时填充对应值。
3. 条件简化：用Select Case整合多场景的条件判断，让代码更简洁易读。
4. 效率优化：调整循环顺序，减少重复读取相同单元格的值，提升运行速度。

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