Find all offsetCells after specific key word?

Question

This is the code i have, but at the line containing the .FindNext expression runtime error 438 shows up. Where is the problem?

Dim bottomCell As Range
Dim offsetCell As Range
With Sheets("C7BB2HD3IINA_NRM_X302")
    Set bottomCell = .Cells.Find(what:="KENNFELD")
    Set offsetCell = bottomCell.Offset(0, 1)
    Set offsetCell = .FindNext(offsetCells)
End With

Answer 1

Using Find/FindNext is complex enough that you should split it out into a separate function which just returns the matches. That way you can focus on the main logic instead of getting tangled up in the Find process.

Try this:

Sub tester()
    
    Dim col As Collection, c
    
    Set col = FindAll(ThisWorkbook.Worksheets("C7BB2HD3IINA_NRM_X302").Cells, _
                       "KENNFELD", xlWhole) 'or xlPart
    
    For Each c In col 'loop over matches
        MsgBox c.Offset(0, 1).Value
    Next c

End Sub


'Find all matches for `val` in `rng` and return as a Collection of cells
Public Function FindAll(rng As Range, val As String, matchType As XlLookAt) As Collection
    Dim rv As New Collection, f As Range
    Dim addr As String

    
    Set f = rng.Find(what:=val, after:=rng.Cells(rng.Cells.CountLarge), _
        LookIn:=xlValues, LookAt:=matchType, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False)
    If Not f Is Nothing Then addr = f.Address()

    Do Until f Is Nothing
        rv.Add f
        Set f = rng.FindNext(after:=f)
        If f.Address() = addr Then Exit Do
    Loop

    Set FindAll = rv
End Function