I am trying to dynamically sum a range of cells based on certain critera via a macro.

Here is my current vba script:

For i = 3 To lastRow
For j = 10 To (Range("H1").Value + 12)
If Range("B" & i) <> "" Then
Cells(i, j).Formula = "=IF(SUM(" & ActiveCell.Offset(1, 0) & ")>=$B$" & i & ", _
SUM(" & ActiveCell.Offset(1, 0) & ")-$B$" & i & ",0)"
End If
Next j
Next i

This is placing the formula in the correct cells for me, but I cannot get the sum to identify the correct range.

In all instances, the range to be summed will be the cell immediately below the cell this formula is pasted into and extends until a blank cell is reached. The sum range will vary from 1 cell to many cells.

The data looks similar to this



5 0 3 0 0 <----Sum Formula 1
1 0 0 0 0 <----Start Sum Range 1
0 0 2 0 0
4 0 1 0 0
0 0 0 0 0 <----End Sum Range 1

7 0 0 9 0 <----Sum Formula 2
7 0 0 3 0 <----Start Sum Range 2
0 0 0 3 0
1 0 0 0 0
1 0 0 3 0 <----End Sum Range 2

If anyone has an idea of how I can write this part of the vba, I could certainly use the help.