formula problem

Scripting Forums AppleScript | Mac OS X
,
#1

Hello,

how can i write the formula =IF(B34=0;“”;((C34-B34)*24)) written in a cell
write in Applescript

I tried this, but that is not good

	set formula of cell "J62" to "=IF(J34=0;"";((J34-H34)*24))"

and how can i write in applescript the formula so that the colum letter changes with the cell were the formula is writen

example
formula in J62: =IF(J34=0;“”;((J34-H34)*24))
formula in K62: =IF(K34=0;“”;((K34-K34)*24))

Can somebody help me

#2

Are you scripting Excel?

#3

Try using R1C1 notation

set formularr1c1 of cell "J62" to "=IF(R34C=0;"";((R34C-R34C[-2])*24))"

if you waant both rows and columns to be relative.

set formularr1c1 of cell "J62" to "=IF(R[-28]C=0;"";((R[-28]C-R[-28]C[-2])*24))"
#4

Hello,

i’m indeed programing “in” excel

the problem isn’t solved, i have still a Syntaxiserror about a expeted row end but found “”

set formularr1c1 of cell "J62" to "=IF(R34C=0;"";((R34C-R34C[-2])*24))"

I am stil looking

#5

Have you tried replacing the semi-colons with commas?

#6

I’ve been looking at this problem in more detail.

  • The double quote that you are looking for. In applescript that would be "" .
  • To refer to a range, range “J62” is the syntax, cell “J62” doesn’t work for me.
    The script posted in #1 doesn’t compile for me, but
tell application "Microsoft Excel"
	set formula of range "J62" to "=IF(J34=0,\"\",((J34-H34)*24))"
end tell

works fine. (If you are not using a US version, you may need to use ; instead of the commas.

It’s not clear if you want to always look at row 34, the formula you put in column B has relative row addressing as well as relative column addressing

Its also not clear what formula you want. Your three examples are incosistant.
in =IF(B34=0;“”;((C34-B34)*24)) the cell being tested is subtracted from the cell to its right
in =IF(J34=0;“”;((J34-H34)*24)) the cell two to the left of the cell being tested is subtracted from the test cell.

This script will put
=IF(B$34=0,“”,(B$34-A$34)*24) in B62
=IF(J$34=0,“”,(J$34-I$34)*24) in J62 and
=IF(K$34=0,“”,(K$34-J$34)*24) in K62

tell application "Microsoft Excel"
	set formulaStr to "=IF(R34C=0,\"\",(R34C-R34C[-1])*24)"
	set formula r1c1 of range "B62" to formulaStr
	set formula r1c1 of range "J62" to formulaStr
	set formula r1c1 of range "K62" to formulaStr
end tell

However this script

tell application "Microsoft Excel"
	set formulaStr to "=IF(R[-28]C=0,\"\",(R[-28]C-R[-28]C[-1])*24)"
	set formula r1c1 of range "B62" to formulaStr
	set formula r1c1 of range "J62" to formulaStr
	set formula r1c1 of range "K62" to formulaStr
end tell

puts =IF(B34=0,“”,(B34-A34)*24) in B62
=IF(J34=0,“”,(J34-I34)*24) in J62 and
=IF(K34=0,“”,(K34-J34)*24) in K62

#7

:D:lol::cool:Thanks Mike,

I found it. You are complely right

Thanks