Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Правила (синтаксис) записи формулы в ячейку, используя VBA
 
#1
11.01.2020 13:47:43
Здравствуйте!
Помогите пожалуйста с формулой массива. С макросами работаю 2й день, в общем опыта работы с макросами нет. Но хотел бы разобраться в текущей ошибке Run-time error '1004': Нельзя установить свойство Formulaarray для класса Range.
Пробывал разбивать на несколько формул общую, так как общая формула больше 255 символов, но не помогло, возможно, что-то неправильно сделал.
Могли бы вы посмотреть формулу и указать на ошибку, или в какую сторону дальше ковырять.
Прикладываю часть массива и кода
 
 
 
#2
11.01.2020 14:15:45
Probabiz, Не совсем понятен вопрос.  Действительно вставить макросом формулу длиннее 255 не получится в случае если это массивная. Существует 4 метода
1. вставить как обычную, а потом симулировать CTRL+SHIFT+ENTER
2. сперва вставить формулу массива не полную, но работающую, а потом заменой заменить её часть , сделав её полной.
3. Использовать формулу в именах и на лист помещать уже имя
4. Sokol92 демонстрировал функцию, которая автоматом итерациями разбирала вложения и делала соответствующие замены, что аналогично №2 но не требует искать что на что можно заменить.

Ну и 5. сократить саму формулу - было 154
=IFERROR(LOOKUP(SUMPRODUCT((ISNUMBER(SEARCH(CHAR(42)&Заявка!C3&CHAR(42);Прайс!$C:$C)))*(Прайс!$F:$F=Заявка!J3);Прайс!$A:$A);Прайс!$A:$A;Прайс!$D:$D);"")
. Вот нафига там CHAR(42) если это "*", вроде не много а 5 символов экономим.  ссылаться на ячейку того же  листа можно без именини листа,  Закрепленные столбцы не нужны
=IFERROR(LOOKUP(SUMPRODUCT((ISNUMBER(SEARCH("*"&C3&"*";Прайс!C:C)))*(Прайс!F:F=J3);Прайс!A:A);Прайс!A:A;Прайс!D:D);"")
, а вот то что диапазон  - полный столбец - это некорректно нужно указывать с ограничением строк иначе страдает быстродействие. Правда мы тем самым раздуем опять формулу, но тут придут на помощь именованные диапазоны, замените  
диапазоны на имена Прайс!A$1:A$1000 на_PrA занеся в имена.
=IFERROR(LOOKUP(SUMPRODUCT((ISNUMBER(SEARCH("*"&C3&"*";_PrC)))*(_PrF=J3);_PrA);_PrA;_PrD);"")
стало 95
Аналогично с длинной.
=IF(IFERROR(LOOKUP(SUMPRODUCT((ISNUMBER(SEARCH("*"&C3&"*";_PrC)))*(_PrF=J3);_PrA);_PrA;_PrG);"")=0;"";IFERROR(LOOKUP(SUMPRODUCT((ISNUMBER(SEARCH("*"&C3&"*";_PrC)))*(_PrF=Заявка!J3);_PrA);_PrA;_PrG);""))
Изменено: БМВ - 11.01.2020 14:46:03
По вопросам из тем форума, личку не читаю.
 
 
 
#3
11.01.2020 14:15:48
А где же лист КоммунСервис?
Изменено: skais675 - 11.01.2020 14:16:21
Мой канал
 
 
 
#4
11.01.2020 14:24:22
Это я не доподчистил файл, пропустил. Лист переименован на "заявка"

БМВ, а как симмулируется CTRL+SHIFT+ENTER?

Цитата
БМВ написал: ...придут на помощь именованные диапазоны, замените  диапазоны на имена Прайс!A$1:A$1000 на_PrA занеся в имена.
Это с помощью операторов DIm и Replace правильно понимаю?
 
 
 
#5
11.01.2020 14:47:05
Probabiz,  Просто сперва измените формулу см. пример. Потом сможете взять образец для макроса.
По поводу симуляции
Нужну симулировать через application.sendkeys для ячейки активной F2 и CTRL+SHIFT+ENTER

посмотрите https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=118970
и http://www.excelworld.ru/forum/10-42409-1
Изменено: БМВ - 11.01.2020 15:09:44
По вопросам из тем форума, личку не читаю.
 
 
 
#6
11.01.2020 16:23:06
Цитата
БМВ написал: Нужну симулировать через  application.sendkeys ...
БМВ, спасибо за подсказки и напутствия! Все формулы укоротил, как Вы и говорили с помощью диспетчера имен. И ошибка Нельзя установить свойство Formulaarray для класса Range пропала. Все заполняется как надо.
Но изначально почему начал переводить все формулы в макросы, из-за того что таких прайсов 6 штук по 50000 строк, и заявка на 500 строк, в общем вычисления производятся очень долго от 30 минут до 1,5 часа в зависимости от компьютера.
Благодаря Вам сейчас все формулы заполняет макрос, но вставляет формулы а как сделать так чтобы он вставлял значения, или как вообще ускорить процесс вычисления?
 
 
 
#7
11.01.2020 16:36:49
Probabiz Исходя из вышесказанного, нужно было все макросом сделать используя массивы - а это совсем другая тема.
Мой канал
 
 
 
#8
11.01.2020 17:01:59
Цитата
Probabiz написал:
30 минут до 1,5 часа
это было из-за использования целых столбцов. Убежден, что сейчас будет в десятки раз быстрее, даже если в именах заменить 10000 на 60000 или даже 10000.
Если к этому добавить неоптимальный формулы, то ….. например цена преображается в
=IFERROR(1/(1/SMALL(IF(ISNUMBER(SEARCH("*"&C3&"*";_PrC));_PrF;"");I3));"") обратите внимание, что не простая функция SMALL применится один раз
да и длинная становится вот такой и не массивной
=IFERROR(1/(1/LOOKUP(SUMIFS(_PrA;_PrC;C3;_PrF;J3);_PrA;_PrG));"")
то даже можно вернуть диапазоны из имен обратно, а скорость вас удивит.

Цитата
skais675 написал:
а это совсем другая тема
- да тема другая, но как обычно сперва фундамент надежный, а потом дом, а тут фундамент подкачал, каков бы технологичный дом небыл - рухнет, что собственно и происходило.
Изменено: БМВ - 11.01.2020 17:06:50
По вопросам из тем форума, личку не читаю.
 
 
 
#9
30.01.2021 19:47:08
Добрый день! Дабы не плодить темы, решил спросить здесь. Ошибка та же, что и у ТС. Но у меня проблема не в длинной формуле, а в чем-то другом, никак не могу понять.
Вот такая строчка работает:
Код
Myregionnew(ArrayofSS(k), i).FormulaArray = "=СУММ(ЛЕВСИМВ(" & SSRange(k, i) & ")"
А вот такая уже нет:
Код
Myregionnew(ArrayofSS(k), i).FormulaArray = "=СУММ(ЛЕВСИМВ(" & SSRange(k, i) & ";1))"
SSRange - диапазон в виде строки типа "A3:A6". Если вводить в ячейку эту формулу вручную, не через VBA, то все работает.
Гуглить по этой проблеме уже замучился, в основном проблема у всех с длиной больше 255 символов. Помогите, пожалуйста, разобраться.
Изменено: 0rtega - 30.01.2021 20:34:32
 
 
 
#10
30.01.2021 20:47:26
Цитата
Кирилл Журенко написал:
Вот такая строчка работает
Сомневаюсь. Свойство FormulaArray не локализовано, должны быть "американские" имена функций и  разделители.
Владимир
 
 
 
#11
30.01.2021 20:50:37
Цитата
Кирилл Журенко написал:
Добрый день! Дабы не плодить темы, решил спросить здесь.
и это не правильно
По вопросам из тем форума, личку не читаю.
 
 
 
#12
30.01.2021 21:18:13
Цитата
sokol92 написал:
Сомневаюсь. Свойство FormulaArray не локализовано, должны быть "американские" имена функций и  разделители.
Напрасно, можете проверить и убедиться сами. За подсказку про локализацию спасибо, исправил, однако результат тот же. На строке:
Код
Myregionnew(ArrayofSS(k), i).FormulaArray = "=SUM(VALUE(LEFT(" & SSRange(k, i) & ";1)))"
выдается та же ошибка.
Цитата
БМВ написал:
и это не правильно
Первый раз такое на форумах встречаю. На всякий случай уточню: мне следует создать аналогичную тему с тем же вопросом, что и у ТС, и описать свою ситуацию? И ее не удалят в связи с пунктом 3.7 Правил?
Изменено: 0rtega - 30.01.2021 21:19:37
 
 
 
#13
30.01.2021 21:35:55
просто у вас вопрос синтаксиса, а именно , разделитель ; должен быть в VBA ,
Код
Myregionnew(ArrayofSS(k), i).FormulaArray = "=SUM(VALUE(LEFT(" & SSRange(k, i) & ",1)))"


а можно
Код
Myregionnew(ArrayofSS(k), i).FormulaArray = "=SUM(VALUE(LEFT(" & SSRange(k, i))))"

или
Код
Myregionnew(ArrayofSS(k), i).FormulaArray = "=SUM(--LEFT(" & SSRange(k, i)))"
По вопросам из тем форума, личку не читаю.
 
 
 
#14
30.01.2021 22:02:34
Цитата
БМВ написал:
просто у вас вопрос синтаксиса, а именно , разделитель ; должен быть в VBA ,
Заработало! :)  Спасибо большое! Получается, это справедливо только для FormulaArray? До этого в обычных формулах (FormulaLocal) в VBA всегда использовал точку с запятой, никогда проблем не возникало...
Изменено: 0rtega - 30.01.2021 22:07:17
 
 
 
#15
31.01.2021 10:14:34
Вы не обращали внимания на то, что тут ключевое - Local. Т.е. формула должна вводиться в той локализации, которая применена в Excel и формула будет работать. А в FormulaArray у Вас где Local? Нигде. А значит надо вводить на "интернациональном" для VBA языке и по его правилам. А этот английский с разделителем аргументов запятая.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#16
31.01.2021 10:29:07
Дмитрий(The_Prist) Щербаков, спасибо большое за разъяснение
 
 
 
#17
31.01.2021 10:29:35
Цитата
0rtega написал:
Для FormulaLocal в VBA в качестве разделителей допускается указывать только точки с запятой
и снова не так. См. выше мой ответ: разделители применяются те, которые установлены в ОС в качестве разделителя функций(списков). Т.е. в точности так же, как Вы их вводите с листа без VBA. А это могут быть и запятые, если локализация изначально английская.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
Страницы: 1
Читают тему
Наверх