result of setFormula() not recognised (solved) [closed]

asked 2016-02-22 16:58:34 +0200

Pansmanser
305 ●42 ●52 ●63

updated 2016-02-22 21:51:25 +0200

The following line of code inserts a formula using the function COUNTIF into cell F43.

 oSheet.getCellByPosition(5,42).setFormula("=COUNTIF(F33:F36,1)")

The formula is inserted in the cell exactly as expected, but the result in the spreadsheet is Err:508. (I tried ""=1"", but the result is the same.) I can manually copy and paste the formula from the formula bar back in to the formula bar and it then works correctly. A similar expression using a SUM function in place of COUNTIF works correctly. Is there some limitation on which functions can be used in setFormula? Or am I missing something?

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-30 12:42:50.634067

Comments

When the sheet is copied from elsewhere, Calc seems to be deciding that the column F is all DATES. This may have something to do with problem, but does not entirely explain it. The problem reproduces in another column. Now there is a second less important question: why is Col F becoming DATES?

Pansmanser ( 2016-02-22 17:15:41 +0200 )edit

add a comment

answered 2016-02-22 18:02:18 +0200

pierre-yves samyn
13131 ●2 ●19 ●72

updated 2016-02-22 18:03:27 +0200

You have to use same separator as Tools▸Options▸Calc▸Formula▸Separators▸Function.

For example, with my french UI :

oSheet.getCellByPosition(5,42).setFormula("=COUNTIF(F33:F36;1)")

So, is it , for you?

Regards

edit flag offensive delete link

Comments

Thank you so much, Pierre-Yves, the semi-colon has fixed it, although, yes, the separator IS defined as comma, so there is a bit of mystery there! Strange also that you can get away with a comma separator when entering the formula manually. (I've fixed the date problem)

Pansmanser ( 2016-02-22 21:50:42 +0200 )edit

add a comment

result of setFormula() not recognised (solved) [closed]

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-30 12:42:50.634067

Comments

1 Answer

Comments

Question Tools

Stats

Related questions

result of setFormula() not recognised (solved) [closed] edit

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-08-30 12:42:50.634067

Comments

1 Answer

Comments

Question Tools

Stats

Related questions

result of setFormula() not recognised (solved) [closed]

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-30 12:42:50.634067