Excel vba - Loop thru a dynamic range, Find value does not go beyond second found value.

Try this...

Sub LastCellInColumnOffsetInsert()
        
    Dim rngSum As Range, FirstFound As String
    
    Set rngSum = Columns("I").Find(What:="SUM", _
                                   LookIn:=xlFormulas, _
                                   LookAt:=xlPart, _
                                   SearchOrder:=xlByRows, _
                                   SearchDirection:=xlNext, _
                                   MatchCase:=False)
                                   
    If Not rngSum Is Nothing Then
        FirstFound = rngSum.Address
        Do
            rngSum.Offset(0, -1).FormulaR1C1 = "=R[-1]C[-7] & ""  TOTAL"""
            Set rngSum = Columns("I").FindNext(After:=rngSum)
        Loop Until rngSum.Address = FirstFound
    End If
    
End Sub

This link is a good guide for the .Find method.
The ultimate search