Excel fill down formula

Scripting Forums AppleScript | Mac OS X
#1

I would like to set the formula in row 1 of column 1 to “wc” and the value of cell1, and then fill down that formula to the remainder of the column in the used range of the active sheet.
The following failed in that it set the value of every cell in column 1 to “WC=A1”

tell application "Microsoft Excel"
    set formula of row 1 of column 1 to "wc" & ("=A1")
    fill down range "A:A"
end tell

How can I correct this script to capture the value of each cell in Column A and modify it by adding the text “wc”?

#2

You have a couple of problems but here is some of the relevant syntax along with the closest solution I can think of.

First, wc=A1 isn’t a formula. It is a string. A formula must begin with the equal sign. There is no reason for the digit to increment when you fill down — the scripting command isn’t as flexible as the GUI. That command would work however if you fed it only a formula, like this:

tell application "Microsoft Excel"
	with timeout of 1 second
		set formula of range "B1" to "=A1"		
		fill down range "B1:B20"
	end timeout
end tell

Each cell from B1:B20 would end up with a formula of =A1 through =A20.

You could use the autofill command and that may get you closer. Once you feed it two cells, it can sense a pattern and autofill the range.

tell application "Microsoft Excel"
	with timeout of 1 second
		set formula of range "A1" to "wc=A1"
		set formula of range "A2" to "wc=A2"
		autofill range "A1:A2" destination range "A1:A20"
	end timeout
end tell

Each cell from A1:A20 would have a formula of wc=A1 through wc=A20. But, since those are strings, this still isn’t what you want.

Your biggest issue is that it isn’t possible for cells to reference themselves (e.g. set formula of range "A1" to "=A1"). You will quickly generate circular reference errors.

I would suggest putting the formula in column B instead, as in the first snippet above but with an addition to the formula like this:

tell application "Microsoft Excel"
	with timeout of 1 second
		set formula of range "B1" to "=CONCATENATE(\"wc\",A1)"
		fill down range "B1:B20"
	end timeout
end tell

You would end up with B1:B20 having the concatenation of “wc” and whatever value is in A1:20.

That all said, you could experiment with something like this:

tell application "Microsoft Excel"
	with timeout of 1 second
		set formula of range "A1" to "wc" & value of cell "A1"
		set formula of range "A2" to "wc" & value of cell "A2"
		autofill range "A1:A2" destination range "A1:A20"
	end timeout
end tell

It’s a variation on two of the snippets above. It concatenates ‘wc’ and the value that’s in cells A1 and A2 and then fills down. The problem is that the results are not easily defined and depend upon what is in range A1:A2, not on what’s in the rest of column A. I would be surprised if it produced a useful outcome.

#3

Thank you Mockman. Your explanations and examples were very helpful.