Excel VBA - 我的查找下一个在我的选择范围内不起作用

Question

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

我有一系列组 1 到多(最多 12 行 - 每组一行接一行(字符串的初始部分重复我不知道它们重复的时间如何，但每当它们出现时，我都需要提取相关数据，这是重复字符串之后的所有内容。现在并非所有 12 行都在组中，有时有 11 行或更少，但它们在数据组中没有空白行，所以我所做的是一旦我找到我的标题行，我就会调用一个子例程来遍历数据块并进行提取，但是当我返回并使用 .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。

输出是(在各个行上(：

大 - $A$2 SML - $B$3 SML - $B$4 SML - $B$5 SML -

$B$7 大 - $A$4 SML - $B$3 SML - $B$4 SML - $B$5 SML - $B$7大 - $A$5 短信 - $B$3 短信 - $B$4 短信 - $B$5 短信 - $B$7大 - $A$7 短信 - $B$3 短信 - $B$4 短信 - $B$5 短信 - $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