excel - error set array formula

Scripting Forums AppleScript | Mac OS X
,
#1

Hey All,


tell application "Microsoft Excel"
	--Formula 1
	-- Target Formula but receive an error
	--set formula array of cell "A1" to "=IF(SUM((F16:F194<0)*(G16:G194=L3)*(D16:D194<>\"VALUE ONE\")*(D16:D194<>\"VALUE TWO \")*1)=0,\"\",SUM((F16:F194<0)*(G16:G194=L3)*(D16:D194<>\"VALUE ONE\")*(D16:D194<>\"VALUE TWO\")*1))"
	
	-- Formula 2
	--Works Removing the last part 
	-- SUM((F16:F194<0)*(G16:G194=L3)*(D16:D194<>\"VALUE ONE\")*(D16:D194<>\"VALUE TWO\")*1) 
	--and replacing it by "1" or anything
	--Suspecting something might not be coded right in last part but unsure what mistake I've made
	
	set formula array of cell "A2" to "=IF(SUM((F16:F194<0)*(G16:G194=L3)*(D16:D194<>\"VALUE ONE\")*(D16:D194<>\"VALUE TWO \")*1)=0,\"\",1)"
	
end tell

Need:

  1. set a formula array to a cell.

Issue:

  1. Receiving error: "Microsoft Excel got an error: Can’t set formula array of cell “A1” to …
  2. Converting the working excel formula to an applescript formula I receive the error above

What works:

  1. using Excel and typing this formula:
    “=IF(SUM((F16:F194<0)(G16:G194=L3)(D16:D194<>“VALUE ONE”)(D16:D194<>"VALUE TWO ")1)=0,“”,SUM((F16:F194<0)(G16:G194=L3)(D16:D194<>“VALUE ONE”)*(D16:D194<>“VALUE TWO”)*1)”

then pressing command+return makes this an formula array WHICH WORKS!

  1. Formula 2 works but only if you remove the last part of the formula
    SUM((F16:F194<0)(G16:G194=L3)(D16:D194<>"VALUE ONE")*(D16:D194<>"VALUE TWO")*1) and you can replace it with anything and the script works

Summary:

  1. I’m suspecting there is an issue with a character in there as I learned you have to "" all " to “"” so there might be something I’m not seeing.

Any help like always would be appreciated.

#2

Thanks for the direction Jacques as always greatly appreciated.

Here’s another solution


tell application "Microsoft Excel"
	activate
		set formula_1 to "=IF(SUM((F16:F194<0)*(G16:G194=L3)*(D16:D194<>\"VALUE ONE\")*(D16:D194<>\"VALUE TWO\")*1)=0,\"\",SUM((F16:F194<0)*(G16:G194=L3)*(D16:D194<>\"VALUE ONE\")*(D16:D194<>\"VALUE TWO\")*1))"
		
		set the clipboard to formula_1
		select cell "A1"
		tell application "System Events"
			keystroke "v" using {command down}
			keystroke "u" using control down
			keystroke return using {command down}
		end tell
end tell