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

Автор Konstanta, 23 октября 2019, 11:49

0 Пользователи и 1 гость просматривают эту тему.

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

#3
Цитата: Bigor от 23 октября 2019, 11:56и разделителем листа/диапазона должна быть .
Это не так: при использовании FormulaR1C1, естественно, используется синтаксис Excel R1C1, в котором Экселевский разделитель листа и диапазона.

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

bigor

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

Konstanta

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

mikekaganski

Цитата: Konstanta от 23 октября 2019, 12:32Исправил в коде запятую на точку с запятой.
Где именно?
Выглядит (исходя из результата и из использованного единственного числа слова "запятую") так, как будто Вы исправили одно место, тогда как предполагалось исправить все такие вхождения.

ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(SEARCH(""ПРОМО"";'ЗАПАСНЫЕ ЧАСТИ'!RC[-19];1));RC[-1];0)"
С уважением,
Михаил Каганский

bigor

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

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

mikekaganski

Цитата: Bigor от 23 октября 2019, 12:35макросе у вас англоязычные формулы, а в пост вы вставляете русскоязычные
Ну так даётся результат из строки формул ЛО еяпп
С уважением,
Михаил Каганский

mikekaganski

Цитата: Bigor от 23 октября 2019, 12:19
Если верить "переводчикам" формул для excel из русского на английский, то разделителем для русскоязычных формул  ; для англоязычных , в LO всегда ; 

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

Konstanta

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

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

Konstanta


mikekaganski

Цитата: Konstanta от 23 октября 2019, 12:48И причины понятны и проблема решена!
Хмм... я бы не спешил с выводами. С учётом, что у меня (русская винда, русская системная локаль, английская локализация Экселя) Эксель именно требует запятых в этом макросе...
С уважением,
Михаил Каганский

bigor

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

mikekaganski

Ну это да; я-то смотрю с т.з. наличия бага и возможности его исправить...
С уважением,
Михаил Каганский