vba - .Findnext 不尊重范围集

Question

在我的代码中，我使用了 .Find 的两个实例, 结合 .FindNext .众所周知，这很容易出错，不幸的是我也不异常(exception)。然而，这是我能想到的最好的。下面的代码，我去掉了最不相关的东西。

问题是有重复的值，我想保留两个，所以我决定使用 .Findnext如果有重复，使用:

If newqst = refqst Then
    Set newqstadrs = Findrange.FindNext(after:=lstqstadrs)
Else

这里的问题是 .FindNext不尊重它应该继续 Findrange.Find , 但继续 FindRangeTwo.Find在这里使用:

newrowtwo = FindRangeTwo.Find(rCell.Value, LookIn:=xlValues, lookat:=xlWhole).row

完整代码:

For o = 72 To lastrow 

    Dim refqst As String
    refqst = wss.Cells(o, 1).Value
    If Not refqst = "" Then
        If InStr(refqst, ".") > 0 Then
            Dim Findrange As Range
            Dim newqst As String
            Dim newqstadrs As Range
            Dim lstqstadrs As Range

            If newqst = refqst Then
                Set newqstadrs = Findrange.FindNext(after:=lstqstadrs)
            Else

                Select Case Left(refqst, 1)
                    Case 1
                        Set Findrange = wsa.Range(wsa.Cells(4, gewaskolom), wsa.Cells(11, gewaskolom))
                    'some more cases here
                End Select
                Set newqstadrs = Findrange.Find(refqst, LookIn:=xlValues, lookat:=xlWhole)
            End If

            If newqstadrs Is Nothing Then
            Else
                newqst = newqstadrs.Value
                Dim newrow As Long
                newrow = Findrange.Find(refqst, LookIn:=xlValues, lookat:=xlWhole).row
                Dim lstqst As String

                If Not wsa.Cells(newrow, 1) = "" Then
                    'do some stuff         
                    lstqst = refqst
                    Set lstqstadrs = newqstadrs

                ElseIf Not wsa.Cells(newrow, 2) = "" Then

                    Dim FindRangeTwo As Range
                    Set FindRangeTwo = wsa.Range(wsa.Cells(newrow, gewaskolom), wsa.Cells(wsa.Range("B" & newrow).End(xlDown).row, gewaskolom))
                    Dim SearchRange As Range
                    Set SearchRange = wss.Range(wss.Cells(o + 1, 1), wss.Cells(wss.Range("B" & o).End(xlDown).row, 1))
                    Dim rCell As Range
                    For Each rCell In SearchRange
                        Dim newrowtwo As Long
                        newrowtwo = FindRangeTwo.Find(rCell.Value, LookIn:=xlValues, lookat:=xlWhole).row
                        'do some more stuff
                    Next rCell
                    lstqst = refqst
                    Set lstqstadrs = newqstadrs
                End If
            End If            

        End If
    End If
Next o

Answer 1

您只能拥有一对 Find/FindNext。第二个覆盖第一个。您需要 FindRangeTwo 的替代方法。鉴于 FindRangeTwo 是单列 (gewaskolom) 并且您正在寻找该行， application.match 应该做得很好。

像这样的东西，

dim newrowtwo as variant   '<~~ should be variant type for IsError to catch

...
newrowtwo = application.match(rCell.Value, FindRangeTwo, 0)
if not iserror(newrowtwo) then
    ...
end if
...

请注意，application.match 返回的是 FindRangeTwo 中的位置，而不是工作表上的行。工作表上的实际行是 (newrowtwo + newrow - 1)。