3

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!

Jonas
  • 121,568
  • 97
  • 310
  • 388
Evan Patrick McCann
  • 31
  • 1
  • 1
  • 2
  • 1
    Range.FindNext seems ultimately broken. It has never worked for me, even using the same code as Microsoft's example. – Nick Bedford Oct 23 '14 at 02:01

3 Answers3

2

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
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
1

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.

Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
  • According to some debug notifications I threw in, FirstTotal is being set correctly about 50% of the time, and the rest of the time is empty. There are no other .Find or .FindNext calls prior to this in the code. Can you think of any reason this might be happening? – Evan Patrick McCann Jun 12 '12 at 13:54
  • We know that means "Total" isn't being found. This means that SearchRange doesn't contain "Total." I'd look to see what the address of SearchRange is when "Total" isn't found. – Doug Glancy Jun 12 '12 at 13:59
0

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.

exceptional exception
  • 527
  • 7
  • 26
Jairo Tovar
  • 1