Slow generating a column of data from a formula using "repeat"

Scripting Forums AppleScript | Mac OS X
#1

Hi,
I am using Applescript with Excel and have run across a performance problem when I do:

-- reset timestamp to start at zero by subtracting first recorded time
tell targetSheet
	repeat with i from 2 to rowCount
		set formula of row i of column 2 to "=A" & i & "-$A$2"
	end repeat
end tell

In column A I have a set of timestamps which I copied from another sheet. In column B I want to reset the timestamps starting at zero time, thus subtracting $A$2 from all the timestamps in column A. This snippet works but it is real slow. I am dealing with, in this case, about 32K rows and in other spreadsheets I will have about 100K or more rows.

When I ran the above I was finally after 20 minutes forced to kill it since it had only done a few thousand cells.

When I do it by hand in Excel, I set B2 = A2-$A$2 and then copy/paste to the end of the column, it takes a 4-5 seconds, but not minutes.

Can I do a paste of the formula over the used range?

thanks.
-frank

#2

Instead of paste you could used fill down, and loose the repeat loop:

set addrTargetRange to "B2:B" & rowCount

tell application "Microsoft Excel" to tell targetSheet
	set formula of row 2 of column 2 to "=A" & i & "-$A$2"
	fill down range addrTargetRange
end tell

But 100k cells…maybe not.

#3

Thank you, that worked splendidly. 28,947 rows were done in bare seconds. I will try on my larger datasets later on. I will update the timing later on.

-frank

#4

As a point of information I was able to handle 302K rows with 6 columns of data in 12 seconds. Can’t imagine how long this would have taken using the repeat loop.

-frank