Formula text treated as variable in AppleScript problem

Question

Created Nov ’21

Replies 3

Boosts 0

Participants 2

I am trying to use AppleScript to set the formula of a cell in Microsoft Excel. I am running into an error but I don't have enough AppleScript knowledge to understand or fix the error and need some help. It seems to be treating part of the formula as a variable. It stops at the word History, colored in green and mentions the variable can't be made into real type in the error. I want the entire string to be treated as text it inserts into the cell. I realized I needed to escape the quotes but it is still not working. I think there is a problem with the commas and the quotes too. Any help would be appreciated!

Tell application "Microsoft Excel"

tell active sheet

	set formula of range "E2" of active sheet to "=IF(J2=/" & History /" & ,  /" & level2 / ",IF(ISNUMBER(SEARCH(/" & ICU / “,level2)),/“ & ICU / ", /" & level3 / "))"

end tell

end tell

Boost

Answer 1

red_menace OP

Nov ’21

I can’t tell what you are trying to do, but the escape character is a backslash - \. You also need to pay attention where the ampersand & is used - in a string (between the quotes) it is just another character, between strings it is the concatenation operator.

0

Answer 2

allenameri OP

Nov ’21

The original excel formula is this: =IF(J2=“History”,”level2,IF(ISNUMBER(SEARCH("ICU”,level2)),"ICU”,”level3)). I want to use the script to insert this as text into the cell E2 but AppleScript won't take the formula as text and instead turns History into a variable even if I put quotes "" around the whole thing.

0

Answer 3

allenameri OP

Nov ’21

God bless you. The backslash was the problem the whole time. How embarrassing! Ty!

0