Макрос изменяет формулу при её записи

[Konstanta]

Приветствую Всех!
Пытаюсь понять причины и решить проблему, с которой в Excel не сталкивался (там просто эта проблема не появлялась):

В ячейке Z1 активного листа, макросом должна встать формула, ссылающаяся на другой лист книги (дело в том, что листы удаляются а потом вставляются новые но с тем же именем. Формула рушится и конечно её надо прописать заново после вставки нового листа):         

=ЕСЛИ(ЕОШИБКА(ПОИСК("ПРОМО";'ЗАПАСНЫЕ ЧАСТИ'!G1;1));Y1;0)         
При наличии в проверяемом выражении слова «ПРОМО» формула возвращает 0, а противном случае = Y1         
В ручном режиме конечно всё работает но, при вставке макросом он почему-то вставляет формулу иного вида:         

=ЕСЛИ(ЕОШИБКА(ПОИСК("ПРОМО",$'запасные части'.g10.1)),Y10)          

Что в итоге приводит к ошибке         Ошибка:508

Код Выделить Развернуть

Sub Макрос5()
'
' Макрос5 Макрос
'

'
   Sheets("Общий").Select
   Range("Z1").Select
   ActiveCell.FormulaR1C1 = _
       "=IF(ISERROR(SEARCH(""ПРОМО"",'ЗАПАСНЫЕ ЧАСТИ'!RC[-19],1)),RC[-1],0)"
   Range("Z2").Select
End Sub

Проблемный файлик вложил.
Прошу помочь понять и исправить.
Подобные проблемы с внесением формул довольно часто могут появляться именно в LO, поэтому прошу знатоков разъяснить, что именно происходит с формулой и как её нужно формулировать в коде, чтобы она верно интерпретировалась макросом при вставке.

[mikekaganski]

Используйте ; вместо , в тексте формулы R1C1.

[bigor]

Для LO (во всяком случае для русской локали, разделитель аргументов в формуле ; у вас ,
и разделителем листа/диапазона должна быть .  

[mikekaganski]

и разделителем листа/диапазона должна быть .

Это не так: при использовании FormulaR1C1, естественно, используется синтаксис Excel R1C1, в котором Экселевский разделитель листа и диапазона.

[EDIT]
И похоже, что Excel ожидает именно запятую при использовании этого синтаксиса ... так что, вероятно, это баг, требующий написания в трекер.

[bigor]

Если верить "переводчикам" формул для excel из русского на английский, то разделителем для русскоязычных формул  ; для англоязычных , в LO всегда ; 

[Konstanta]

Исправил в коде запятую на точку с запятой.
результат тот же: =ЕСЛИ(ЕОШИБКА(ПОИСК("ПРОМО";$'запасные части'.g10.1)),Y10)
только теперь вместо запятой точка с запятой
он добавил знак доллара перед $'запасные части'.g10.1)),Y10)
и убирает точку с запятой из исходной формулы (Y1;0) получая в итоге "Y10"
также после имени листа в оригинальной формуле стои восклицательный знак, который макрос превратил в точку.
кроме того странно, но он изменил регистр букв с больших на маленькие
Догадка посетила: а может макрос всё делает верно, но локальные настройки LO что-то меняют (типа дописать формулу и пр.)

[mikekaganski]

Исправил в коде запятую на точку с запятой.

Где именно?
Выглядит (исходя из результата и из использованного единственного числа слова "запятую") так, как будто Вы исправили одно место, тогда как предполагалось исправить все такие вхождения.

Код Выделить Развернуть

ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(SEARCH(""ПРОМО"";'ЗАПАСНЫЕ ЧАСТИ'!RC[-19];1));RC[-1];0)"

[bigor]

У меня после исправлений , на ; макрос отрабатывает верно. Интересно другое в макросе у вас англоязычные формулы, а в пост вы вставляете русскоязычные.

зы проверил, LO переводит сам из английских в русские

[mikekaganski]

макросе у вас англоязычные формулы, а в пост вы вставляете русскоязычные

Ну так даётся результат из строки формул ЛО еяпп

[mikekaganski]

Если верить "переводчикам" формул для excel из русского на английский, то разделителем для русскоязычных формул  ; для англоязычных , в LO всегда ; 

Тут надо хорошо понять, что делает Эксель в этом случае. В нативном API ЛО всегда используется стандартный набор - английские названия функций, точка с запятой в качестве разделителя и т.п. А вот реализуя VBA, надо "соответствовать". И тут вопросы. ЛО знает 17(!) грамматик формул (не считая спецзначений); для R1C1 есть две грамматики (English и Native). Какую использовать? (В настоящий момент используется Native.) Правильно ли они реализованы? Так что надо проверять Эксель на разных настройках: разный язык UI Экселя, разные региональные настройки, разный язык ОС... и тогда только будет понятно.Ну, или искать документацию с чётким описанием (я не искал).

[Konstanta]

Где именно?
Выглядит (исходя из результата и из использованного единственного числа слова "запятую") так, как будто Вы исправили одно место, тогда как предполагалось исправить все такие вхождения.

Точно!!!
Внёс исправления во всей формуле, заменив запятую на точку с запятой.
Всё работает ровно!!
Спасибо огромное.
И причины понятны и проблема решена!

[Konstanta]

Всем большое Спасибо!
Задачка решена.

[mikekaganski]

И причины понятны и проблема решена!

Хмм... я бы не спешил с выводами. С учётом, что у меня (русская винда, русская системная локаль, английская локализация Экселя) Эксель именно требует запятых в этом макросе...

[bigor]

С учетом, что файл ods, может в excel его открывать не планируют.

[mikekaganski]

Ну это да; я-то смотрю с т.з. наличия бага и возможности его исправить...