Страницы: 1
RSS
Распознавание функции на русском языке в английской версии в VBA при использовании функции СЦЕПИТЬ
 
Добрый день!

Возникла следующая проблема. Я написал макрос, который вставляет в ячейку формулу путем VBA (делаю это макросом, поскольку у меня таких формул несколько и файл будет тяжелым, если они будут по умолчанию, а так макрос вставляет формулы и меняю затем их на значение).

Пример формулы в VBA следующий:
Код
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(""=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС('"",Ссылки!R7C5,""SI'!$BG:$BG;ПОИСКПОЗ("",R2C2,"";'"",Ссылки!R7C5,""SI'!$BH:$BH;0));ИНДЕКС('"",Ссылки!R6C5,""SI'!$BG:$BG;ПОИСКПОЗ("",R2C2,"";'"",Ссылки!R6C5,""SI'!$BH:$BH;0)));ИНДЕКС('"",Ссылки!R5C5,""SI'!$BG:$BG;ПОИСКПОЗ("",R2C2,"";'"",Ссылки!R5C5,""SI'!$BH:$BH;0)))"")"
    
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    With Intersect(ActiveSheet.UsedRange, [B7:B7])
        .NumberFormat = "dd/mm/yyyy"
        .HorizontalAlignment = xlCenter
        .FormulaLocal = .FormulaLocal
    End With
    
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Вставляю формулу, затем перевожу ее в формат даты и меняю на значение. Поскольку используется функция СЦЕПИТЬ, то внутри формулы другие функции распознаются как текст, на русском языке. Возможно ли это как-то обойти? У кого-то из коллег стоит русская версия Excel, а у кого-то английская, необходимо, чтобы работало в обеих версиях.

Спасибо.
 
Цитата
stevie44 написал:
У кого-то из коллег стоит русская версия Excel, а у кого-то английская, необходимо, чтобы работало в обеих версиях.
Тогда не стоит использовать локализованные функции, нужно использовать ENG
У вас все равно  ошибки
FormulaR1C1Local нужно, а не FormulaR1C1, если вы используете русскоязычные функции
"=CONCATENATE(""=ЕСЛИОШИБКА( - это бред,  равно там появляться не должно.
По вопросам из тем форума, личку не читаю.
 
БМВ,в смысле ошибка? Я через функцию СЦЕПИТЬ сделал готовую формулу, которая является динамической, поскольку она ссылается на лист "Ссылки", где в заданных ячейках прописан путь к файлам, из которых берутся данные, а эти файлы изменяются 1 раз в месяц.
Формула прекрасно работает в русской версии. Раз она работает, значит ошибки нет.
Если прописываю формулу в VBA на английском, включая функции внутри СЦЕПИТЬ, то они не работают в русской версии.
 
stevie44,
Цитата
stevie44 написал:
У кого-то из коллег стоит русская версия Excel, а у кого-то английская, необходимо, чтобы работало в обеих версиях.
Однозначно надо использовать только английские версии формул.
Вы не приложили файл-пример, и создавать файл с нужными именами листов, чтобы перевести вашу формулу на "родной" язык - не хочется.
Почитайте
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=109027&TITLE_SEO=109027-vydelenie-konstant-zamena-na-formuly-raschet-i-vyvod-kak-konstanty-vba&MID=903754#message903754

https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=108849&TITLE_SEO=108849-giperssylki-na-100-listov-avtomatom&MID=902221#message902221
 
stevie44, если
Код
     ActiveCell.FormulaR1C1 = _        "=CONCATENATE(""=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС('"",Ссылки!R7C5,""SI'!$BG:$BG;ПОИСКПОЗ("",R2C2,"";'"",Ссылки!R7C5,""SI'!$BH:$BH;0));ИНДЕКС('"",Ссылки!R6C5,""SI'!$BG:$BG;ПОИСКПОЗ("",R2C2,"";'"",Ссылки!R6C5,""SI'!$BH:$BH;0)));ИНДЕКС('"",Ссылки!R5C5,""SI'!$BG:$BG;ПОИСКПОЗ("",R2C2,"";'"",Ссылки!R5C5,""SI'!$BH:$BH;0)))"")"
работает хоть где либо прописывая формулу в ячейку
=CONCATENATE(""=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС('"",Ссылки!R7C5,""SI'!$BG:$BG;ПОИСКПОЗ("",R2C2,"";'"",Ссылки!R7C5,""SI'!$BH:$BH;0));ИНДЕКС('"",Ссылки!R6C5,""SI'!$BG:$BG;ПОИСКПОЗ("",R2C2,"";'"",Ссылки!R6C5,""SI'!$BH:$BH;0)));ИНДЕКС('"",Ссылки!R5C5,""SI'!$BG:$BG;ПОИСКПОЗ("",R2C2,"";'"",Ссылки!R5C5,""SI'!$BH:$BH;0)))"")
то я подарю вам свою самокрутку.
По вопросам из тем форума, личку не читаю.
 
БМВ,
если  эту формулу затем вставить как значение и перевести формат, то все  прекрасно работает. В 1-м посте я указал целиком код, и все работает не  первый год.
Прилагаю пример, где в ячейке B7 вставка осуществляется макросом, а в ячейке B8 та же формула, скопированная и вставленная как значение (в 1-м посте это отображено в VBA, только там я делаю все в ячейке B7)
Изменено: stevie44 - 18.09.2018 12:38:18
 
Не, самокрутку не отдам.
Вместо этого просто собрать формулу при помощи & вместо Concantinate
Ссылки!$E$7 и подобное сразу заменить на значения еще в коде.
Изменено: БМВ - 18.09.2018 12:56:10
По вопросам из тем форума, личку не читаю.
 
Цитата
stevie44 написал:
макрос вставляет формулы и меняю затем их на значение
Честно говоря не понял смысл в этих танцах с бубном. Зачем макросом вставлять формулу, а потом вручную менять её на значение? Может проще макросом сразу вставлять вычисленное значение?
Не стреляйте в тапера - он играет как может.
 
Ts.Soft,БМВ, знал бы я как - сделал бы))) Я не умею писать формулы в VBA, только макрорекодером...
 
Цитата
Ts.Soft написал:
Зачем макросом вставлять формулу, а потом вручную менять её на значение?
Часто проще вставить макросом формулу, вычислить и заменить значением, чем писать 100500 строк кода для реализации этой функции в VBA. Сам так регулярно делаю.
Bite my shiny metal ass!      
 
Лузер™, Как медведь, далекий от VBA :-) , тут реально танцы вокруг копи пэст, ибо собрать формулу, потом  её результат вставить как формулу и потом результат снова вставить как значение, при этом еще и проблемы с локализацией нажить - это уже перебор
Изменено: БМВ - 18.09.2018 17:35:56
По вопросам из тем форума, личку не читаю.
 
stevie44, 1) включаем запись макроса 2) вбиваем формулу 3) выключаем запись макроса 4) радуемся и копируем полученный код.
 
Цитата
Лузер™ написал:
Часто проще вставить макросом формулу, вычислить и заменить значением, чем писать 100500 строк кода для реализации этой функции в VBA
А ещё проще использовать в коде Application.WorksheetFunction...
 
StoTisteg, а вот тут, простите,  поправочка, не так и проще, да и список этих функций немного ограничен.
Тогда уж Evaluate
По вопросам из тем форума, личку не читаю.
 
StoTisteg, так не получится.
У меня раз в месяц меняется название файла, на который я ссылаюсь на листе "Ссылки".
К примеру, в сентябре я ссылаюсь на файлы июля, августа и сентября:
Код
=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС('K:\DATA\2018\09\Reports\[SI_30.09.2018_for work.xlsx]SI'!$BG:$BG;ПОИСКПОЗ(;'K:\DATA\2018\09\Reports\[SI_30.09.2018_for work.xlsx]SI'!$BH:$BH;0));ИНДЕКС('K:\DATA\2018\08\Reports\[SI_31.08.2018_for work.xlsx]SI'!$BG:$BG;ПОИСКПОЗ(;'K:\DATA\2018\08\Reports\[SI_31.08.2018_for work.xlsx]SI'!$BH:$BH;0)));ИНДЕКС('K:\DATA\2018\07\Reports\[SI_31.07.2018_for work.xlsx]SI'!$BG:$BG;ПОИСКПОЗ(;'K:\DATA\2018\07\Reports\[SI_31.07.2018_for work.xlsx]SI'!$BH:$BH;0)))
А в октябре буду ссылаться на файлы августа, сентября и октября. Для этого я сделал на листе ссылки формулы с адресами, при изменении месяца меняется адрес на файл. С помощью функции СЦЕПИТЬ я соединяю ссылку на нужные мне файлы и прописываю в формуле ИНДЕКС+ПОИСКПОЗ диапазоны из тех файлов. Иного способа, как это сделать, я не нашел. Но работает это либо только в русской версии Excel, либо только в английской, если в VBA изменить текстовые названия функций на английские.
Выход тут, как я понимаю, один: прописать формулу в VBA без использования макрорекодера, а я этого не умею делать.
 
stevie44,  очень трудно без данных проверить
попробуйте так
Код
With Worksheets("Ссылки")
ActiveCell = Evaluate("=IFERROR(IFERROR(INDEX('" & .[e7] & "SI'!$BG:$BG,MATCH(" & [b2] & ",'" & .[e7] & "SI'!$BH:$BH,0)),INDEX('" & _
.[e6] & "SI'!$BG:$BG,MATCH(" & [b2] & ",'" & .[e6] & "SI'!$BH:$BH,0))),INDEX('" & .[e5] & "SI'!$BG:$BG,INDEX(" & _
 [b2] & ",'" & .[e5] & "SI'!$BH:$BH,0)))")
End With
Это без вмешательства в вашу формулу.
По вопросам из тем форума, личку не читаю.
 
БМВ,не сработал... Выдал #ЗНАЧ
 
stevie44, тупанул, там же у вас на другие книги ссылки

Попробуйте так
Код
With Worksheets("Ссылки")
ActiveCell.Formula = "=IFERROR(IFERROR(INDEX('" & .[e7] & "SI'!$BG:$BG,MATCH(" & [b2] & ",'" & .[e7] & "SI'!$BH:$BH,0)),INDEX('" & _
.[e6] & "SI'!$BG:$BG,MATCH(" & [b2] & ",'" & .[e6] & "SI'!$BH:$BH,0))),INDEX('" & .[e5] & "SI'!$BG:$BG,INDEX(" & _
 [b2] & ",'" & .[e5] & "SI'!$BH:$BH,0)))"
End With 

'ActiveCell.calculate
'ActiveCell=ActiveCell.value
Если формула будет правильной, то последние два коммента снять.
Изменено: БМВ - 19.09.2018 19:33:25
По вопросам из тем форума, личку не читаю.
 
БМВ, ОГРОМНОЕ СПАСИБО!!!!
Вот теперь супер, все работает!
На будущее - буду знать, как это прописать, чтобы лишний раз не писать на форуме.
 
stevie44,  Всегда рад помочь,
маленький коммент ActiveCell.calculate для подстраховки стоит, сильно не мешает, но может и не помогать вовсе.
По вопросам из тем форума, личку не читаю.
 
БМВ, да-да, я так и понял ) Спасибо!)
Страницы: 1
Наверх