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

问题描述

我正在尝试使用 .Find 和 .FindNext 来搜索单列数据.我首先需要找到包含值Total"的第一个单元格.我试图到达的单元格是总计"单元格之后的第三个单元格，其中包含值技术".可以肯定的是 Cells(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.

我怀疑这里的答案非常基本，但我对 excel 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!

推荐答案

这会有帮助吗?

主题:Excel VBA中的.Find和.FindNext

Topic: .Find and .FindNext In Excel VBA

链接:http://www.siddharthout.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屋！