Write a formula that subtotals data that is below this summation row

where is the data to be summed in relation to the text containing the
searched for text?

Is the sum from the row after this row down to the next empty cell.

Assume the found text is in C3 and we are in F3. the numbers to sum are in
D4:D20 and D21 is blank

set rng = cells.find("ABC")
' rng is a reference to C3
set rng1 = rng.offset(0,3)
set rng2 = rng.offset(1,1)
rng1.formula = "=Sum(" & range(rng2,rng2.end(xldown)).Address(1,1) & ")"

--
Regards,
Tom Ogilvy



" wrote:

I have a sheet which has been copied from a pivot table. I need it to
perform the following:

Find a cell with specific text (Yes)
Move over three columns (I have been using an offset)
Write a formula that subtotals data that is below this summation row
Copies this formula to right until the column is empty
Display this data onto a new summary sheet


Ultimately, this would be a loop that looks in column C for some
wildcard, subtotals and then, moves over to column B and totals the
previously calc'd subtotals.


I seem to be able to get to the specific text cell and use the offset.
I am struggling with the formula as the rows that the formulas or
subtotals would be on are never the same row #'s. If the source data
that I copied were from an original formula-based spreadsheet, this
would be easier for me.


Any help would be appreciated greatly.