Finding the average of two generated totals

[PVANS]

Good afternoon,

I am currently using the following code:

Dim r As Range

For Each r In
Sheet1.UsedRange.Columns(1).SpecialCells(xlCellTypeConstants).Areas
With r(r.Rows.Count + 1).Resize(, 8)
.Value = .Offset(-1).Value
.Offset(, 1).Resize(, 1).Value = WorksheetFunction.Sum(r.Offset(, 1))
.Offset(, 3).Resize(, 1) = WorksheetFunction.Sum(r.Offset(, 3))
.Offset(, 4).Resize(, 1) = WorksheetFunction.Sum(r.Offset(, 4))
.Font.Bold = True
End With
Next r

to summarise groups of like transations (that are seperated by blank rows)
by calculating the totals and values of each set and placing them in the row
below said group of transactions
eg: (bold doesn't seem to come out, the bottom row is the totals/summary)

DATE QTY B/S PRICE Gross Sale CUR ACC SERVICE
20090727 16997 S 66.5 1130300.5 GBX DEFAULT DMA
20090727 48606 S 66.5 3232299 GBX DEFAULT DMA
20090727 10000 S 66.5 665000 GBX DEFAULT DMA
20090727 58316 S 66.5 3878014 GBX DEFAULT DMA
20090727 133919 S 266 8905613.5 GBX DEFAULT DMA

This process is repeated for each group of transactions (20+) that are on
the worksheet.

However, my Price column total is not meant to be a sumtotal, but instead
the average which is the Gross Sales/QTY.

Is there a way I can modify my code to do that?

Would really appreciate any help or suggestions that can be given.

Regards,

PVANS

 

[Dave Peterson]

Instead of using worksheetfunction.sum(), try worksheetfunction.average().