I have a report that contains 10,000+ rows of information. I have a macro
written that will insert subtotals (Data...Subtotal) to create logical breaks
in the data. Part of the data is additive, therefore can be easily summed
onto the Subtotal row. However, within the subtotal row, I also need to be
able to divide two numbers together. For example, here is what the report
looks like:

Col A Col B Col C Col D
Spending$ # Units Spending/Unit
Name A $1000 100 $10.00
Name A $2000 50 $40.00
Name A $3000 200 $15.00
--------------------------------------------------------------------------
Name A Total $6000 350 (blank)

When Excel inserts the subtotal row, it sums the Spending$ and # Units
columns, but it cannot create the Spending/Unit calculation. I tried to
write code that would "filter" the subtotal rows after they were inserted,
then copy/paste the appropriate formula across all filtered subtotal rows in
column D, but I got a circular reference error as it appeared that when I did
the copy/paste, it tried to paste over the rows that were filtered out. Is
there a better way for me to insert this Spending/Unit calculation into
column D for ONLY the subtotal rows, leaving the hard-coded values for the
other rows intact?

What you are asking for will be an uphill battle with subtotals. A pivot
table with a calculated field would be a whole pile easier. If you need help
with that just ask...
--
HTH...

Jim Thomlinson


"jday" wrote:

I have a report that contains 10,000+ rows of information. I have a macro
written that will insert subtotals (Data...Subtotal) to create logical breaks
in the data. Part of the data is additive, therefore can be easily summed
onto the Subtotal row. However, within the subtotal row, I also need to be
able to divide two numbers together. For example, here is what the report
looks like:

Col A Col B Col C Col D
Spending$ # Units Spending/Unit
Name A $1000 100 $10.00
Name A $2000 50 $40.00
Name A $3000 200 $15.00
--------------------------------------------------------------------------
Name A Total $6000 350 (blank)

When Excel inserts the subtotal row, it sums the Spending$ and # Units
columns, but it cannot create the Spending/Unit calculation. I tried to
write code that would "filter" the subtotal rows after they were inserted,
then copy/paste the appropriate formula across all filtered subtotal rows in
column D, but I got a circular reference error as it appeared that when I did
the copy/paste, it tried to paste over the rows that were filtered out. Is
there a better way for me to insert this Spending/Unit calculation into
column D for ONLY the subtotal rows, leaving the hard-coded values for the
other rows intact?

This won't really work given the nature of how users will need to work with
this data. They have areas of input & need to be able to see the whole
picture as they are doing this (vs. a pivot table view). I just gave you the
basics of the specific issue I am trying to deal with. Will just do some
creative trial and error to see what I can come up with. Thanks anyway....

"Jim Thomlinson" wrote:

What you are asking for will be an uphill battle with subtotals. A pivot
table with a calculated field would be a whole pile easier. If you need help
with that just ask...
--
HTH...

Jim Thomlinson


"jday" wrote:

I have a report that contains 10,000+ rows of information. I have a macro
written that will insert subtotals (Data...Subtotal) to create logical breaks
in the data. Part of the data is additive, therefore can be easily summed
onto the Subtotal row. However, within the subtotal row, I also need to be
able to divide two numbers together. For example, here is what the report
looks like:

Col A Col B Col C Col D
Spending$ # Units Spending/Unit
Name A $1000 100 $10.00
Name A $2000 50 $40.00
Name A $3000 200 $15.00
--------------------------------------------------------------------------
Name A Total $6000 350 (blank)

When Excel inserts the subtotal row, it sums the Spending$ and # Units
columns, but it cannot create the Spending/Unit calculation. I tried to
write code that would "filter" the subtotal rows after they were inserted,
then copy/paste the appropriate formula across all filtered subtotal rows in
column D, but I got a circular reference error as it appeared that when I did
the copy/paste, it tried to paste over the rows that were filtered out. Is
there a better way for me to insert this Spending/Unit calculation into
column D for ONLY the subtotal rows, leaving the hard-coded values for the
other rows intact?