mark_h82
10-11-2006, 06:07 AM
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
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