Thread: Relative Summation formula

I wrote a very short program that inserts a blank row at every change in date. I then move over to the column that contains the revenue figures for the records for that last date. I want to insert a summation formula that adds up the rows above the inserted row all the way up to the first blank row above. I recorded a macro that uses the sum (sigma) button in the Formula tab and it works fine, but just for the first set of rows because it did it absolutely, i think (just for that set or rows). When I run the program it doesn't add up the correct number of amounts for the desired set or rows (all with the same date). If I were manually inserting such a formula for each set of separated rows, it would correctly calculate the sum (up to the first blank cell above).

how can I insert a sum formula into my code that always adds the amounts from the inserted (blank) row to the first blank row above?

Thank you,


Gary

You would need to upload a sample workbook - something like the following could be adapted to your needs however a sample workbook would make things much easier - if we assume the values are in column F you could find first row with a value then look for next blank row and insert the formula there. If you can adapt the following then great otherwise a sample workbook with no sensitive data/dummy workbook would make things easier. In the attached workbook run the macro "InsertSum"

Option Explicit

Sub insertSum()
Dim fRow As Range, stRow As Long
With Columns("F")
Set fRow = .Find(what:="*", after:=.Cells(1, 1), LookIn:=xlFormulas)
End With
stRow = fRow.Row
Set fRow = Range("F" & fRow.Row).Resize(Cells(Rows.Count, "F").End(xlUp).Row).SpecialCells _
     (xlCellTypeBlanks)
Range("F" & fRow.Row).Formula = "=Sum(R[-1]C:R[-" & fRow.Row - stRow & "]C)"
End Sub

Subtotal by date first, then subtotal by type and make sure to uncheck the 'Replace existing subtotals' option.