> 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

20 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

"(E-Mail Removed)" 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.

>

>