Excel subtotals-how to calculate with result?

Scripting Forums AppleScript | Mac OS X
#1

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

#2

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

#3

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
#4

What is rawDataRange? How is that value derived?

#5

It is essentially the current sheet’s used range.

#6

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.

#7

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