search for ! then sum...

[Wayne]

The following is an example of the data that I require a
sum of all the parts.
Column A Consists of part numbers, Column B Qty.
I need to search for " ! " in column A and in the
corresponding B cell sum all the above cells untill abut
not including a cell that also contains text ie(2727.30m2.
then search for the next "!" in column A and sum again...

This can go on for some thousands of rows.

Any ideas anyone?

Stage/Level EXTERNAL PERIMETER/1
AREA 2727.30m2
101106 22
101109 14
101112 28
101118 88
109624 32
109837 71
109838 26
109839 86
109841 2
109850 11
SP9005 1
SP9007 1
SP9008 2
SP9010 2
!
Stage/Level ROOF PARAPET/1
AREA 187.20m2
101118 4
101133 4
101500 16
109624 12
109810 6
109837 4
109838 2
109839 26
!
Stage/Level INT PARTY WALLS/1
AREA 297.60m2
101115 10
101118 4
101500 28
109624 30
109837 6
109839 30
109841 12
!

 

[Tom Ogilvy]

This works if
2727.30m2 is in column B:

Sub GetSum()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, cell As Range
Dim ar As Range
Set rng = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
Set rng1 = rng.Offset(0, 1).SpecialCells(xlBlanks)
Set rng2 = Range("B3")
For Each ar In rng1.Areas
Set cell = ar(1, 1)
cell.Formula = "=Sum(" & _
Range(rng2, cell.Offset(-1, 0)).Address & ")"
Set rng2 = cell.Offset(2, 0)
Next
End Sub

At least it works with your data with the first entry in cell A1.