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.

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.

Thank you for your quick answer.

The data to be summed would be directly below the sum formula so I
could modify the code that you have written to meet this.

However, when I copy from a pivot table, I do not have a static number
of cells to sum and I would have subtotals in the data. I am trying to
avoid having to insert rows after every sub category.

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

--
Regards,
Tom Ogilvy




" wrote:

Thank you for your quick answer.

The data to be summed would be directly below the sum formula so I
could modify the code that you have written to meet this.

However, when I copy from a pivot table, I do not have a static number
of cells to sum and I would have subtotals in the data. I am trying to
avoid having to insert rows after every sub category.

I am getting a cells not found error on the set rng 3 line?? Below is
an example of the spreadsheet. Exp. Onetime and Exp. Ong. would be
sums in Col. E (of the data below it). Col. B would then be a
summation of the subtotals. I am using a find to get to the exp. lines
and then, the offset to get over to Col. E.

Col. B Col. C Col. D Col. E

Yes 610,781
EXPENSE ONETIME 454,856
CONTRACT LABOR 205,967
OTHER DIRECT 140,146
EXPENSE ONGOING 155,924
EQUIPMENT DEPR 41,446
OTHER EQUIPMENT 114,479

My mistake, I found my error. Thank you.