Any help would be great. I have a data that has the word
summary(1),summary(2),summary(3) and so on . These
summary are located on the A column
and the value of the three cells above should be use in
sub total for the summary cells. I do not want to use
the =sum( formula since the cell number of summary
changes , however,
the word summary and it (?) are always unique. I will
like to have a macro that will find the word summary(1)
and sub total just the 3 above corresponding value . and
find summary(2) and total just the 3 corresponding value
above summary(2), and find summary(3) and total just the
3 corresponding value above summary(3). This is just a
example:
A1 B1
CD $100
PEN $ 20
PENCIL $ 5
Summary(1) ?
PAPER $100
PEN 10
ENVELOPES $60
Summary(2) ?
PEN $ 20
CANDLE $10
CUP $5
Summary(3) ?

Is anyone able to help?
Please & Thanks in advance!!

Try this. It uses the SUM function but adjusts as necessary.

'=============================================
Sub test()
Dim FoundCell As Object
With ActiveSheet.Columns(1)
Set FoundCell = .Find("Summary", LookIn:=xlValues)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
ActiveSheet.Cells(FoundCell.Row, 2).FormulaR1C1 = _
"=SUM(R[-3]C:R[-1]C)"
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
End With
End Sub
'=============================================


---
Message posted from http://www.ExcelForum.com/