Find/Findnext not returning results

[bertjeiv]

hello I try to find all occurrences of "spare" inside a certain range in all shapes and forms "xxspareyy" should also be returned, i believe lookat:=xlPart takes care of that? but i dont get any results. not even if the cell value is "spare"

here is the code lr1 is last row and lc1 is last column

    needle = "spare"
    spares = 0
    With Range(Cells(10, 1), Cells(lr1, lc1))
        Set lastcell = .Cells(.Cells.count)
    End With
    
    Set foundcell = Range(Cells(10, 1), Cells(lr1, lc1)).Find(What:=needle, after:=lastcell, LookAt:=xlPart, MatchCase:=False)
    If Not foundcell Is Nothing Then
        FirstAddr = foundcell.Address
        spares = spares + 1
    End If
    Do Until foundcell Is Nothing
        Debug.Print foundcell.Address
        Set foundcell = Range(Cells(10, 1), Cells(lr1, lc1)).FindNext(after:=foundcell)
        If foundcell.Address = FirstAddr Then
            Exit Do
        End If
        spares = spares + 1
    Loop
    MsgBox spares & " #spares"

I think I'm missing something stupid here but cant find it
Thanks

 

[texasalynn]

what happens when you manually do a find?

 

[John_w]

For certain arguments, Find uses the settings from the last Find (in the dialogue box or calling the method) if you omit the arguments - see the Help. Do a manual Find with the Macro Recorder and use the generated code in your code.

 

[MrKowz]

Try:

    needle = "spare"
    spares = 0
    With Range(Cells(10, 1), Cells(lr1, lc1))
        Set lastcell = .Cells(.Cells.Count)
    End With
    With Range(Cells(10, 1), Cells(lr1, lc1))
        Set foundcell = .Find(What:=needle, LookIn:=xlValues, LookAt:=xlPart)
        If Not foundcell Is Nothing Then
            firstaddr = foundcell.Address
            spares = spares + 1
            Do
                Debug.Print foundcell.Address
                Set foundcell = .FindNext(after:=foundcell)
                spares = spares + 1
            Loop While Not foundcell Is Nothing And foundcell.Address <> firstaddr
        End If
    End With
    MsgBox spares & " #spares"