[Solved] Invalid name error in valid macro inserted formula

[sokolowitzky]

I have this formula in cell AD2;

I paste it with a group of other formulas, all others work exactly as desired, but this one does not calculate correctly until I re-edit it (without changing, I just press F2 and then Enter)
I think it must be because of COUNTIF, but I can not solve this. The only solution I could find to this issue(except editing content) is changing each "=" with itself (ctrl+H)
Does someone might have an idea to solve this?

Code: Select all

    ADdwa = oSheet.getcellrangebyname("AD2")
    ADdwa.formula= "=IF(AA2="&""""""&";"&""""""&";AA2+COUNTIF(AA$2:AA2;AA2)*0,01)"

[Zizi64]

Code: Select all

"&""""""&"

What is this?



Please upload a real sample file here in ODF format.

[sokolowitzky]

Actually it looks like this on the spreadsheet

Code: Select all

=IF(AA2="";"";VALUE(AA2)+(COUNTIF(AA$2:AA2;AA2)*0,01)) 

Please check AD2 of Sheet1. Just click on the cell and open function wizard pop up(ctrl+f2)and press enter again.

Here I attach it . I don't want to add macro, because people who will see this later might avoid opening it. instead, I write below the macro code that it supposed to contain, so people can try themselves.

Code: Select all

Dim dizmacr
oSheet=thiscomponent.sheets(0)
    ADdwa = oSheet.getcellrangebyname("AD2")
    ADdwa.formula= "=IF(AA2="&""""""&";"&""""""&";VALUE(AA2)+(COUNTIF(AA$2:AA2;AA2)*0,01))"
end sub

[Zizi64]

Code: Select all

=IF(AA2="";"";VALUE(AA2)+(COUNTIF(AA$2:AA2;AA2)*0,01))

Your formula uses "English" function names, but non-English decimal separator character. My LibreOffice 6.1.1 is adjusted to Hungarian locale (with comma dec. sep.), but with English function names in the Calc application.

I just modified the "formula" to "formulalocal" and it works!

Code: Select all

ADdwa.formulalocal= "=IF(AA2="&""""""&";"&""""""&";VALUE(AA2)+(COUNTIF(AA$2:AA2;AA2)*0,01))"

Or it works too:

Code: Select all

ADdwa.formulalocal= "=IF(AA2=" & Chr(34) & Chr(34) & ";"  & Chr(34) & Chr(34)  & ";VALUE(AA2)+(COUNTIF(AA$2:AA2;AA2)*0,01))"

[Lupp]

This should be what you needed:

Code: Select all

="IF(AA2="""";"""";VALUE(AA2)+(CONTIF(AA$2:AA2)*0.01))"

What good for?

An ordinary doublequote is represented by two subsequent doublequotes inside a string constant.
The not localised .Formula property will expect the decimal point.

(I will not again play with sheets formatted in this absurd way. Spreadsheets have cells and I want to see them.)

[sokolowitzky]

Mister Kovacs, i don't know if you write in this forum just for leisure. But you are doing great. I really appreciate all your help.
Thanks a lot.
@Lupp. Thanks. The reason I don't use gridlines is becaue I feel like they kill my focus. Instead I change the bckground colur in the areas I use.
Thanks for your answer.

[Zizi64]

i don't know if you write in this forum just for leisure.

Yes, it is my hobby, and it is my special method of learning - 'two in one'.

[Lupp]

I missed to emphasise that the #NAME? error alert should always occur if an otherwise correct formula contains a numeric constant with a decimal separator not complying with the locale chosen under 'Language Settings'. This is not changed by a different language setting for the document or for cells.

For the .Formula property of a cell I would expect that the point as the decimal separator is mandatory.
(You may experiment with the .FormulaLocal property.)