用于多次遍历相同范围的If循环

Question

上下文:我有一系列的单元格，其中有些有相同的内容。我试图遍历单元格，并根据该值找到匹配的次数(最多为7次)采取特定的操作。如果有帮助，我希望根据找到的匹配将某些信息范围移到不同的行。

我所做的:我让它在范围内循环，但问题是它会多次击中相同的范围。

我想要的是:如果可能的话，我想在匹配值结束后继续For循环。

For i = 1 To lastRow
        If Cells(i, 4).Value = Cells(i + 1, 4).Value Then                   'Checks first and second levels
            Debug.Print "First and second levels ["; Cells(i, 4).Address & "," & Cells(i + 1, 4).Address & "]"

            If Cells(i, 4).Value = Cells(i + 2, 4).Value Then           'Checks second and third levels
                Debug.Print "Second and third levels ["; Cells(i, 4).Address & "," & Cells(i + 2, 4).Address & "]"

                If Cells(i, 4).Value = Cells(i + 3, 4).Value Then       'Checks third and fourth levels
                    Debug.Print "Third and fourth levels ["; Cells(i, 4).Address & "," & Cells(i + 3, 4).Address & "]"

                Else
                    'If Cells(i, 14).Value = "No" Then                        'Only first & second level procedures

                    Debug.Print "Only first and second levels ["; Cells(i, 4).Address & "," & Cells(i + 2, 4).Address & "]"
                End If

            Else
                If Cells(i, 14).Value = "No" Then                           'Only first & second level procedures


                End If
            End If
        Else
            Debug.Print "No match [" & Cells(i, 4).Address & "," & Cells(i + 1, 4).Address & "]"
        End If
     Next i
'End With
End Sub

这就是当前Debug.Print输出的样子(D8:D11完全匹配)：

一级和二级$D$8，$D$9

二级和三级$D$8，$D$10

三级和四级$D$8，$D$11

一级和二级$D$9，$D$10

二级和三级$D$9，$D$11

只有一级和二级$D$9，$D$11

Answer 1

好的，修复很简单:根据匹配的值更改i整数值。代码如下

For i = 2 To lastRow
        If Cells(i, 4).Value = Cells(i + 1, 4).Value Then                   'Checks first and second levels
            Debug.Print "First and second levels ["; Cells(i, 4).Address & "," & Cells(i + 1, 4).Address & "]"

            If Cells(i, 4).Value = Cells(i + 2, 4).Value Then           'Checks second and third levels
                Debug.Print "Second and third levels ["; Cells(i, 4).Address & "," & Cells(i + 2, 4).Address & "]"

                If Cells(i, 4).Value = Cells(i + 3, 4).Value Then       'Checks third and fourth levels
                    Debug.Print "Third and fourth levels ["; Cells(i, 4).Address & "," & Cells(i + 3, 4).Address & "]"

                Else
                     i = i + 2             '<~~~CHANGE
                    If Cells(i, 14).Value = "No" Then                        'Only first & second level procedures

                        Debug.Print "Only first and second levels ["; Cells(i, 4).Address & "," & Cells(i + 2, 4).Address & "]"
                    End If
                End If

            Else
                i = i + 1                     '<~~~CHANGE
                If Cells(i, 14).Value = "No" Then                           'Only first & second level procedures

                End If
            End If
        Else
            Debug.Print "No match [" & Cells(i, 4).Address & "," & Cells(i + 1, 4).Address & "]"
        End If
     Next i
End Sub

Answer 2

1. 打开一个新的sheet2来保存数据。
2. 通过sheet1中的第4列。
3. 如果发现与sheet2中的任何列匹配，则在该列下添加地址。否则，在sheet2中创建一个具有此值的新列，并在其下添加地址。