Martin Fishlock
12/19/2006 3:14:00 AM
Nigel
Try this little one.
Sub gettotals()
Const cszColAc As String = "B" ' name col
Const cszColSum As String = "Y" ' sum col
Const cszSheetDataname As String = "Sheet1" 'data sheet
Const cszSheetTotalname As String = "Sheet2" ' result sheet
Const clRowDataStart As Long = 3 ' data start row
Const clRowTotalStart As Long = 2 ' total start row
Dim wst As Worksheet 'total worksheet
Dim wsd As Worksheet 'data worksheet
Dim lRowData As Long ' current data row
Dim lRowDataEnd As Long 'end of data row
Dim lrowTotal As Long ' current total row
Dim szNameCur As String
Dim lRowCur As Long
Dim bFirst As Boolean
Set wsd = Worksheets(cszSheetDataname)
Set wst = Worksheets(cszSheetTotalname)
lrowTotal = clRowTotalStart
lRowDataEnd = wsd.UsedRange.Rows.Count + wsd.UsedRange.Row - 1
'set initial
szNameCur = wsd.Cells(clRowDataStart, cszColAc)
lRowCur = clRowDataStart
bFirst = False
For lRowData = clRowDataStart + 1 To lRowDataEnd
If bFirst = False And _
wsd.Cells(lRowData, cszColAc) = _
szNameCur Then 'got the end
wst.Cells(lrowTotal, 1) = szNameCur
wst.Cells(lrowTotal, 2).Formula = _
"=sum('" & cszSheetDataname & "'!" & _
cszColSum & lRowCur & ":" & _
cszColSum & lRowData & ")"
lrowTotal = lrowTotal + 1
bFirst = True
ElseIf bFirst = True And _
wsd.Cells(lRowData, cszColAc) <> "" Then 'got a new one
szNameCur = wsd.Cells(lRowData, cszColAc)
lRowCur = lRowData
bFirst = False
End If
Next lRowData
End Sub
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.
"Nigel" wrote:
> I have data in a spreadsheet that is grouped by Customer and between each
> grouping there are 2 blank lines
>
> What I have to do is calculate the sum of the a column (for example Y) so
> for the first customer it would be sum(y7:y23) and for the next customer it
> would be sum(y26:y30) but when I rerun the data it might be (Y7:y15) and
> (y18:y22) and so forth depending on the conditions I apply
>
> Is there any way I can calculate the number of records each grouping
> contains, In column B at the top of the group will be the customer name and
> in column B at the end of the group the customer name will appear again if
> that helps to calculate the number of rows inclusive of the top and bottom
> rows