# Excel subtotals-how to calculate with result?

My script creates a sheet with a number of rows and asks Excel to subtotal them by given columns (Sales etc). What I am trying to do after that is to perform a division by another number on the Sales subtotal and insert it into the sheet. That way for each subtotal X, I will have a second number appear in a field below it, e.g. X/100.

Any suggestion as to how would be much appreciated.

I can’t find any way to refer to the subtotals derived, for one thing.

Dennis

As I understand it, your Subtotal is realy the total of an entire column of data, so finding it should be doable.

Thanks Mike,

My code follows (in a tell block for the active sheet)- I think the variables are obvious.
it is sorting the rows by a product type, then subtotalling a bunch of monthly sales columns.

Dennis

``````
sort rawDataRange order1 sort ascending key1 column 10 order2 sort ascending key2 column 1 order3 sort ascending key3 column 2 header header yes without match case

tell rawDataRange
subtotal group by 10 function do sum total list {9, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22}
end tell

``````

What is rawDataRange? How is that value derived?

It is essentially the current sheet’s used range.

Perhaps

``````tell application "Microsoft Excel"

set columnList to {9, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22}

tell rawDataRange
subtotal group by 10 function do sum total list columnList
end tell
-- end of existing script

set screen updating to false

set keyColumn to (get entire column of (column (10) of rawDataRange))

set grandTotalCell to find keyColumn what "Total" after (row (1) of keyColumn) look in values look at part search direction search previous with match case

set foundCell to grandTotalCell
set ActUpon to find keyColumn what "Total" after (foundCell) look in values look at part search direction search previous with match case

repeat until (first row index of grandTotalCell) â‰¤ (first row index of ActUpon)

set insertRange to (intersect range1 entire row of (get offset ActUpon row offset 1) ¬
range2 entire column of rawDataRange)

insert into range insertRange

repeat with colNum in columnList
set oneCell to get column colNum of insertRange
set formulaStr to formula of (get offset oneCell row offset -1)
set formula of oneCell to formulaStr & "/100"
end repeat

set foundCell to ActUpon
set ActUpon to find keyColumn what "Total" after (foundCell) look in values look at part search direction search previous with match case
end repeat
set screen updating to true
end tell
``````

It doesn’t disturb the Grand Total, but it’s not fast.

Dividing by 100 can often be done by inspection. This might be an unnecessary row.

Thanks Mike, this is brilliant. It works out of the box nearly. I’ve learned some good methods from it, hopefully it will help others as well.

Dennis