excel - .FindNext 在 .Find 函数之后失败(excel vba)

Question

我正在尝试使用 .Find 和 .FindNext 搜索单列数据。我首先需要找到包含值“Total”的第一个单元格。我试图访问的单元格是“总计”单元格之后包含值“Tech”的第三个单元格。可以肯定的是，Cells(1, 1) 不包含“Tech”或“Total”。

Dim FirstTotal As Range
Dim SearchRng As Range
Dim ResultRng As Range
Set SearchRng = Range("A:A")

Set FirstTotal = SearchRng.Find(What:="Total", After:=Cells(1, 1), SearchDirection:=xlNext)
Set ResultRng = SearchRng.Find(What:="Tech", After:=FirstTotal, SearchDirection:=xlNext)
SearchRng.FindNext().Activate
SearchRng.FindNext().Activate

大约有 50% 的时间我运行此代码时，会因以 Set ResultRng = 开头的行中的类型不匹配错误而停止。其余时间，代码一直运行完毕，但结果看起来好像最后两行代码被完全忽略了。

我怀疑这里的答案非常基本，但我对 excel vba 还很陌生，到目前为止我发现的资源都没有回答这个问题。请帮忙!

Answer 1

这有帮助吗？

Sub Sample()
    Dim oRange As Range, aCell As Range, bCell As Range
    Dim ws As Worksheet
    Dim SearchString As String, FoundAt As String

    On Error GoTo Err
    Set ws = Worksheets("Sheet3")
    Set oRange = ws.Columns(1)

    SearchString = "2"

    Set aCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
        Set bCell = aCell
        FoundAt = aCell.Address
        Do
            Set aCell = oRange.FindNext(After:=aCell)

            If Not aCell Is Nothing Then
                If aCell.Address = bCell.Address Then Exit Do
                FoundAt = FoundAt & ", " & aCell.Address
            Else
                Exit Do
            End If
        Loop
    Else
        MsgBox SearchString & " not Found"
    End If
    MsgBox "The Search String has been found at these locations: " & FoundAt
    Exit Sub
Err:
    MsgBox Err.Description
End Sub