# excel - error set array formula

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.

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

``````