VBA to add subtotal with variable range

Question

I'm trying to figure out excel VBA code to add subtotal (=SUBTOTAL(9,__:_) formulas to multiple Total Rows within a worksheet. The issue is that the range for the subtotals can be any height. Per the image below, the hope is that the VBA code would be able to identify when "Total" is present in column B and then proceed to add a subtotal formula in that row for columns E to J. The subtotal formula would need to pull the above balances up until there is a blank cell in order to calculate the amount for the particular client (the length of the range is signified by the arrows in the image below). The range can litterally be any number of rows so has to be fully variable. Any help in this would be greatly appreciated!!

Updated Sample

Answer 1

This can be accomplished with a simple SUBTOTAL across a dynamic range. Locating Total in column B can be looped.

Option Explicit

Sub totalAllClients()
    Dim rng As Range, rngsb As Range, addr As String

    With Worksheets("sheet11")
        With Intersect(.Columns(2), .UsedRange)
            Set rng = .Find(What:="total", After:=.Cells(1), MatchCase:=False, _
                            LookAt:=xlWhole, SearchDirection:=xlPrevious)
            If Not rng Is Nothing Then
                addr = rng.Address(0, 0)
                Do
                    rng.Offset(0, 1).FormulaR1C1 = _
                        "=index(c2, match(""zzz"", r1c2:r[-1]c2))"
                    rng.Offset(0, 3).Resize(, 6).FormulaR1C1 = _
                        "=subtotal(109, r[-1]c:index(c, match(""zzz"", r1c2:r[-1]c2)))"
                    Set rng = .FindNext(After:=rng)
                Loop Until rng.Address(0, 0) = addr

                rng.Offset(2, 3).Resize(1, 6).FormulaR1C1 = _
                    "=aggregate(9, 3, r2c:r" & rng.Row & "c)"
            End If
        End With
    End With

End Sub