Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 22 Feb 2016 21:50:42 +0100result of setFormula() not recognised (solved)https://ask.libreoffice.org/en/question/64864/result-of-setformula-not-recognised-solved/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?
Mon, 22 Feb 2016 16:58:34 +0100https://ask.libreoffice.org/en/question/64864/result-of-setformula-not-recognised-solved/Comment by Pansmanser for <p>The following line of code inserts a formula using the function COUNTIF into cell F43.</p>
<pre><code> oSheet.getCellByPosition(5,42).setFormula("=COUNTIF(F33:F36,1)")
</code></pre>
<p>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?</p>
https://ask.libreoffice.org/en/question/64864/result-of-setformula-not-recognised-solved/?comment=64867#post-id-64867When 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?Mon, 22 Feb 2016 17:15:41 +0100https://ask.libreoffice.org/en/question/64864/result-of-setformula-not-recognised-solved/?comment=64867#post-id-64867Answer by pierre-yves samyn for <p>The following line of code inserts a formula using the function COUNTIF into cell F43.</p>
<pre><code> oSheet.getCellByPosition(5,42).setFormula("=COUNTIF(F33:F36,1)")
</code></pre>
<p>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?</p>
https://ask.libreoffice.org/en/question/64864/result-of-setformula-not-recognised-solved/?answer=64871#post-id-64871Hi
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?
RegardsMon, 22 Feb 2016 18:02:18 +0100https://ask.libreoffice.org/en/question/64864/result-of-setformula-not-recognised-solved/?answer=64871#post-id-64871Comment by Pansmanser for <p>Hi</p>
<p>You have to use same separator as <code>Tools</code>▸<code>Options</code>▸<code>Calc</code>▸<code>Formula</code>▸<code>Separators</code>▸<code>Function</code>.</p>
<p>For example, with my french UI :</p>
<pre><code>oSheet.getCellByPosition(5,42).setFormula("=COUNTIF(F33:F36;1)")
</code></pre>
<p>So, is it <code>,</code> for you?</p>
<p>Regards</p>
https://ask.libreoffice.org/en/question/64864/result-of-setformula-not-recognised-solved/?comment=64881#post-id-64881Thank 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)Mon, 22 Feb 2016 21:50:42 +0100https://ask.libreoffice.org/en/question/64864/result-of-setformula-not-recognised-solved/?comment=64881#post-id-64881