CODEWITHSUNDEEP
excelvbafind
Evan Patrick McCann
Evan Patrick McCann

Reputation: 31

.FindNext failing after a .Find function (excel vba)

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

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.

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!

Upvotes: 3

Views: 26871

Answers (3)

Siddharth Rout
Siddharth Rout

Reputation: 149287

Would this help?

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

Upvotes: 2

Jairo Tovar
Jairo Tovar

Reputation: 1

I have observed the FindNext method fail to find the next occurrence of the searched item when applied in a function with parameters, which is invoked from a cell. The Find method (to search the first occurrence) does work as expected.

Upvotes: 0

Doug Glancy
Doug Glancy

Reputation: 27478

If "Total" isn't found, then FirstTotal will be Nothing, which will result in a Type Mismatch when you try to use FirstTotal for the "After" argument in the ResultRange Find (the 2nd line). This will prevent that error:

Set FirstTotal = SearchRng.Find(What:="Total", After:=Cells(1, 1), SearchDirection:=xlNext)
If Not FirstTotal is Nothing Then
   Set ResultRng = SearchRng.Find(What:="Tech", After:=FirstTotal, SearchDirection:=xlNext)
End If

Generally speaking any dependent Finds need to be treated this way.

Clearly, some kind of Else statement is required here, but I don't know what that would be.

Upvotes: 1

Related Questions