VBA to Sum Contiguous Cells

[tmart]

I'm trying to sum the contents of contiguous cells that have values resulting from a formula.

Before Macro
Cell: Value
M2: 4
M3: 6
M4:""
M5:""
M6: 8
M7: 9
M8: ""
M9:""

Cells M4, M5, M8 and M9 are not empty. These cells have formulas that return "". All cells in this column have the same formula.

After Macro:
M4: 10, M8: 17

A different challenge is that I also need M9 to calculate the sum of M4 and the results of other subtotals in Column M, except M8 (the last sub-total cell). M9=10. M9 is always two cells below the last cell that contains a value in the column.

I would like to post the file or an image, but I can't figure it out. Thanks in advance!

 

[tmart]

I figured out a solution. However, along the way, I came to the conclusion that a more elegant solution would be inserting the formula for the cells when the formula is applicable and then using this code to calculate the Sub total. Can someone give me a clue as to how to assign a formula to the cells that have formulas that result in numbers? Right now, I have another macro inserting my formulas by copying and pasting before I run the SpecialCells below!

In other words, if the result of my target cell with formula = column F x (column T + S) for that row is a number (as opposed to ""), then enter that value. If not, then use SpecialCells to sum the rows directly above with numbers.

My current code:
Dim area As Range
For Each area In Columns(13).SpecialCells(xlCellTypeFormulas, xlNumbers).Areas
With area
Cells(.Rows.Count + .Row, 13).Formula = "=SUM(" & .Address & ")"
End With
Next area