.FindNext在.Find函数后失败（excel vba） [英] .FindNext failing after a .Find function (excel vba)

问题描述

我正在尝试使用 .Find 和 .FindNext 来搜索单列数据。我首先需要找到包含值Total的第一个单元格。我想要访问的单元格是Total单元格中包含值Tech之后的第三个单元格。众所周知，细胞（1,1）不包含Tech或Total。

I am trying to use .Find and .FindNext to search through a single column of data. I first need to find the first cell containing the value "Total". The cell I'm trying to get to is the third cell AFTER the "Total" cell to contain the value "Tech". It is known for certain that the Cells(1, 1) does not contain "Tech" or "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 = 开始。在其余的时间里，代码一直运行，但结果看起来好像最后两行代码完全被忽略了。

About 50% of the times I've run this code, I've been stopped by a type mismatch error on the line beginning with Set ResultRng =. The rest of the time, the code has run all the way through, but the results look as though the final two lines of code were ignored completely.

我怀疑这里的回答很简单，但我很擅长于vba，而我迄今为止发现的任何资源已经回答了这一点。请帮助！

I suspect that the answer here is pretty elementary, but I'm pretty new to excel vba and no resources I've found so far have answered this. Please help!

推荐答案

这是否有帮助？

主题：.Find和.FindNext在Excel VBA中

Topic: .Find and .FindNext In Excel VBA

链接： http://www.siddharthrout.com/2011/07/14/find-and-findnext- in-excel-vba /

提取链接

Sub Sample()
    Dim oRange As Range, aCell As Range, bCell As Range
    Dim ws As Worksheet
    Dim ExitLoop As Boolean
    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 While ExitLoop = False
            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
                ExitLoop = True
            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

这篇关于.FindNext在.Find函数后失败（excel vba）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！