Excel VBA - 我选择范围内的 findnext 无法正常工作

Question

你好，如果有任何人可以帮助解决这个问题

我有一组从 1 到多的组(最多 12 行 - 每组后一行)字符串的初始部分重复我不知道它们重复的时间，但每次我需要提取相关的data 是重复字符串之后的所有内容。现在并非所有 12 行都在组中，有时有 11 行或更少，但它们在数据组中没有空白行，所以我所做的是一旦我找到我的标题行，我就会调用一个子程序来遍历数据 block 并执行我的提取，但是当我回来并使用 .findnext(v) 时，它不会转到下一个标题

    With big_rng      ' this is column A selected
        Set v = .Find("Submarket:", LookIn:=xlValues, LookAt:=xlPart)
        If Not v Is Nothing Then
            firstAddress = c.Row
            Do
                Call this1(need, c.Row, tech, daily_date)
                Set v = .FindNext(v)
                need = need + 1
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With

当我调用 this1 时，我正在做的是选择另一个范围，因为我不知道我的数据是否按正确的顺序排列我使用另一个选择

    Cells(i, 1).Select ' I know which row my group starts and I select down

    Range(Selection, Selection.End(xlDown)).Select

    ' check for substrings and copy across if the substring exists e.g.

    With Selection

    Set d = .Find("Degradation =", LookIn:=xlValues, LookAt:=xlPart)

            If Not d Is Nothing Then
                spos = InStrRev(Cells(d.Row, 1), "=")

                If Mid(Cells(d.Row, 1), spos + 1, 1) = " " Then
                    output_sht.Cells(n, 5) = Right(Cells(d.Row, 1), Len(Cells(d.Row, 1)) - (spos + 1))
                Else
                    output_sht.Cells(n, 5) = Right(Cells(d.Row, 1), Len(Cells(d.Row, 1)) - spos)
                End If

            Else
                output_sht.Cells(n, 5) = "Error in Data"
            End If

当这个 Sub 结束时，我的原始选择消失了(它是 A:A 列)，并且我的 .findnext(v) 给了我上一组的最后一行而不是下一组的第一行(如果存在的话)

我如何通过 findnext 循环，同时保持我原来的选择完好无损

先感谢您

罗伯特

Answer 1

我玩了一下，想出了这段代码。
第 1 行必须高于搜索范围(即标题)，因此数据位于地址 A2:A10 和 B2:B10 中。
我放了Submarket:在 A2 , A4 , A5 & A7和 Sumarket1:在 B3 , B4 , B5 & B7 .

输出是(在单独的行上):

big - $A$2 sml - $B$3 sml - $B$4 sml - $B$5 sml - $B$7 big - $A$4 sml - $B$3 sml - $B$4 sml - $B$5 sml - $B$7 big - $A$5 sml - $B$3 sml - $B$4 sml - $B$5 sml - $B$7 big - $A$7 sml - $B$3 sml - $B$4 sml - $B$5 sml - $B$7

Sub test2()
    Dim big_rng As Range
    Dim v As Variant
    Dim foundrow As Long

    Set big_rng = Sheet1.Range("A1:A10")
    With big_rng
        Set v = .Find("Submarket:", LookIn:=xlValues, LookAt:=xlPart)
        If Not v Is Nothing Then
            Do
                foundrow = v.Row
                Debug.Print "big - " & v.Address
                this1
                'Resize the search range to ignore rows already searched.
                With big_rng.Resize(big_rng.Rows.Count - foundrow + 1).Offset(foundrow - 1)
                    Set v = .Find("Submarket:", LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext)
                End With
            Loop While Not v Is Nothing And v.Row <> foundrow
        End If
    End With

End Sub

Sub this1()

    Dim sml_rng As Range
    Dim v As Variant
    Dim firstAddress As String

    Set sml_rng = Sheet1.Range("B1:B10")

    With sml_rng
        Set v = .Find("Submarket1:", LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext)
        If Not v Is Nothing Then
            firstAddress = v.Address
            Do
                Debug.Print "sml - " & v.Address
                Set v = .FindNext(v)
            Loop While Not v Is Nothing And v.Address <> firstAddress
        End If
    End With

End Sub