sum previous rows based on cell value

[mark_h82]

Hi,

Quite a complicated one so hope I can explain well. I have a spreadsheet where every 60 rows (using row height) I input To Summary in column J, then in column M, N & O I need to some the previous rows from the last some.

EG Cell J58 I have To Summary, therefore I need to some row 2:57 and in M, N & O place the formula. The next cell To Summary is in could be J117, J170 etc. I would then need to some in 59:116, 118:169 etc. I have got this code, but all it does is some from row 1:57, 1:116, 1:169, therefore the sums are not correct.

Anyone any ideas?

Here is the code:



Sub Summs()
FinalRow = ActiveSheet.Range("J65536").End(xlUp).Row
Dim HeadHeight As Double
Dim tHeight As Double
Dim n As Long
HeadHeight = Cells(1, 1).RowHeight
tHeight = 0
Dim cfind As Range
Dim j As Integer
Set cfind = Cells.Find(what:="To Summary", lookat:=xlWhole)
For n = 1 To FinalRow 'ActiveSheet.UsedRange.rows.Count
tHeight = tHeight + Cells(n, 1).RowHeight
If tHeight > (752.25 + HeadHeight) Then
If Application.WorksheetFunction.Count(rows(n - (n - 2) & ":" & n - 1)) > 0 Then
For j = cfind.Row To FinalRow
myrange = ("M" & j - (j - 1) & ":" & "M" & (n - 2))
myrange2 = ("N" & j - (j - 1) & ":" & "N" & (n - 2))
myrange3 = ("O" & j - (j - 1) & ":" & "O" & (n - 2))
If Range("J" & n - 1).Value = "To Summary" Then
Range("M" & n - 1).Formula = "=Sum(" & myrange & ")"
Range("N" & n - 1).Formula = "=Sum(" & myrange2 & ")"
Range("O" & n - 1).Formula = "=Sum(" & myrange3 & ")"
tHeight = HeadHeight + Cells(n, 1).RowHeight
End If
Next j
End If
End If
Next n
End Sub

Thanks, Mark

[mdmackillop]

Hi Mark,
I've not tried to follow your HeadHeight code. The following will insert relevant formulae at each "To Summary" position.


Rw = 1
With Worksheets(1).Range("J:J")
Set cfind = Cells.Find(what:="To Summary", after:=Range("J1"), lookat:=xlWhole)
If Not cfind Is Nothing Then
firstAddress = cfind.Address
Do
cfind.Offset(, 3).Resize(, 3).Formula = "=SUM(R" & Rw & "C:R[-1]C)"
Rw = cfind.Row + 1
Set cfind = .FindNext(cfind)
Loop While Not cfind Is Nothing And cfind.Address <> firstAddress
End If
End With

[mark_h82]

Hi, I have tried this and it is working perfectly. I have ignored the headheight from this query so it seems that wasn't even necessary.
THanks for your help, Mark :)