# formula problem

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

Are you scripting Excel?

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))"
``````

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

Have you tried replacing the semi-colons with commas?

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

:D:lol:Thanks Mike,

I found it. You are complely right

Thanks