Страницы: 1
RSS
Вставка формулы массива в ячейку с помощью VBA
 
Добрый вечер, господа!
Записал макрорекордером действие и получил вот это:
Код
Sheets("ИтогТопливо").Select
    Range("F16").Select
    Selection.FormulaArray = "=SUMIFS(ОтчТопливо!R12C6:R100C6,ОтчТопливо!R12C3:R100C3,MIN(IF(ОтчТопливо!R12C1:R100C1=(CONCATENATE(RC[-2],"" Гос. Номер "",RC[-1])),ОтчТопливо!R12C3:R100C3)),ОтчТопливо!R12C9:R100C9,MAX(IF(ОтчТопливо!R12C1:R100C1=(CONCATENATE(RC[-2],"" Гос. Номер "",RC[-1])),ОтчТопливо!R12C9:R100C9)))"
Но самое главное то, что я запускаю этот макрос и выдает ошибку
"Run time error 1004
Метод Select из класса Range завершен неверно"


Что делаю не правильно?
Изменено: ILUHA - 25.12.2015 21:55:36
 
вот выкладываю сам тестовый файлик.
 
перенесите макрос в отдельный модуль... и уберите array
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Михаил Лебедев, я как-то понял да не понял...
Получается я создаю новый модуль(макрос) в котором пишу этот код. А как мне вызвать макрос именно в тот момент, когда мне это нужно?
Возможно так:
- при нажатии кнопки выполняются всякие мои действия, а потом в конце каким-то образом запустить этот макрос? Или не правильно?
 
Михаил Лебедев, смог запустить таким кодом:
Код
Workbooks.Application.Run "Итог.xlsm!Mod2"
где Mod2 - это отдельный модуль с таким кодом:
Код
Sub Mod2()
    Sheets("ИтогТопливо").Select
    Range("F16").Select
    Selection = "=SUMIFS(ОтчТопливо!R12C6:R100C6,ОтчТопливо!R12C3:R100C3,MIN(IF(ОтчТопливо!R12C1:R100C1=(CONCATENATE(RC[-2],"" Гос. Номер "",RC[-1])),ОтчТопливо!R12C3:R100C3)),ОтчТопливо!R12C9:R100C9,MAX(IF(ОтчТопливо!R12C1:R100C1=(CONCATENATE(RC[-2],"" Гос. Номер "",RC[-1])),ОтчТопливо!R12C9:R100C9)))"
End Sub
Работает, код вставляется, но самое главное НО то, что вставляется не как массив, а просто формула, а мне так не подходит...
 
Почитайте о вставке формул: FormulaR1C1, FormulaLocal,  FormulaArray

P.S. А в первом собщении FormulaArray...
P.P.S.
Цитата
... и уберите array
Видимо, я чего-то не понял...
 
vikttur, так вроде как читал. Если Вы намекаете на то, что нужно использовать  FormulaArray, то я с этого начал эту тему и пытался использовать её, но у меня вылетает ошибка. Или я что-то не понял?
 
Код
Sub Mod2()
    Sheets("ИтогТопливо").Range("F16").FormulaArray = "=SUMIFS(...)"
End Sub
There is no knowledge that is not power
 
SuperCat, я так тоже пробовал...ошибка

"Run time error 1004

Нельзя установить свойство FormulaArray класса Range"
 
Отзовитесь пожалуйста....
 
Код
Sub Макрос1()
'
' Макрос1 Макрос
'

'
    Selection.FormulaArray = "=SUM(R[-22]C:R[-1]C)"
End Sub


у меня все работает

вставить в модуль это:
не нужно оскорблять.
 
аналогия с Range
Код
Sub Макрос2()
'
' Макрос2 Макрос
'

'
    Range("B26").Select
    Selection.FormulaArray = "=SUM(R[-22]C:R[-1]C)"
End Sub
не нужно оскорблять.
 
по колдовал проблема не в синтаксисе а в самой формуле, что то в ней ВБА не понимает
Код
"=SUMIFS(ОтчТопливо!R12C6:R100C6,ОтчТопливо!R12C3:R100C3,MIN(IF(ОтчТопливо!R12C1:R100C1=(CONCATENATE(RC[-2],"" Гос. Номер "",RC[-1])),ОтчТопливо!R12C3:R100C3)),ОтчТопливо!R12C9:R100C9,MAX(IF(ОтчТопливо!R12C1:R100C1=(CONCATENATE(RC[-2],"" Гос. Номер "",RC[-1])),ОтчТопливо!R12C9:R100C9)))"
не нужно оскорблять.
 
Возможно у Вас срабатывает ограничение символов в ячейке. Попробуйте обозвать листы покороче. ОтчТопливо=ОтчТ
 
gling, а не знаете какое количество разрешено?
 
Где то видел про 255. Здесь не Excel запрещает, а VBA.
Попробовал Вашу формулу вставить макросом, выдал ошибку, уменьшил название листа, ошибка на вышла. Поэтому и предложил попробовать.
Изменено: gling - 26.12.2015 02:13:54
 
У меня возникала аналогичная проблема по длине формулы и гуру этого форума предложили решение создать именованною формулу, а в макросе использовать конструкцию типа Сall "имя формулы". ILUHA, если Вы сможете показать, как выглядит Ваша формула на листе (именно формула на на листе и желательно в русской локации офиса), я мог бы попытаться показать вышеописанное решение. Мне тогда помогло :)
 
Цитата
1.(#6)vikttur написал: Цитата: "и уберите array"
Видимо, я чего-то не понял...
Да нет, это я не понял... И запутал всех :( Сорри...

Цитата
2. (#5)ILUHA написал: смог запустить таким кодом: Workbooks.Application.Run "Итог.xlsm!Mod2"
можно вместо всего этого просто написать
Код
Mod2
Цитата
где Mod2 - это отдельный модуль
Mod2 - это не модуль, а процедура. Которая должна лежать в каком-н. модуле (например в Module1).
Если имя процедуры - уникальное, то можно не писать весь путь к ней.
Если же в нескольких модулях есть процедуры с одинаковым именем, тогда так: Module1.Mod2
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
Цитата
ILUHA написал: gling, а не знаете какое количество разрешено?
сия секретная информация запрятана очень глубоко в недрах встроенной справки:
Цитата
Remarks
If you use this property to enter an array formula, the formula must use the R1C1 reference style, not the A1 reference style (see the second example).
The FormulaArray property also has a character limit of 255.
добраться практически невозможно - нужно встать курсором в коде на это свойство и нажать F1.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
ikki, я не думаю, что стОит так ёрничать, т.к. вопрос по поводу количества символов в ячейке - это так, вопрос в формате диалога с человеком, и я не делал из этого вопроса тему на этом форуме.
Спасибо за понимание.
 
Михаил Лебедев, Александр Сергеевич, gling,  заметил такую ситуацию. Вот код:
Код
Sheets("ИтогТопливо").Select
    Range("F16").Select
    Selection.FormulaArray = _
        "=SUMIFS(ОтчТопливо!R12C7:R100C7,ОтчТопливо!R12C4:R100C4,MIN(IF(ОтчТопливо!R12C2:R100C2=RC[-2],ОтчТопливо!R12C4:R100C4)),ОтчТопливо!R12C1:R100C1,MAX(IF(ОтчТопливо!R12C2:R100C2=RC[-2],ОтчТопливо!R12C1:R100C1)))"

Sheets("ИтогТопливо").Select
    Range("F15").Select
    Selection.FormulaArray = _
        "=SUMIFS(ОтчТопливо!R12C6:R15C6,ОтчТопливо!R12C3:R15C3,MIN(IF(ОтчТопливо!R12C1:R15C1=(CONCATENATE(RC[-2],"" Гос. Номер "",RC[-1])),ОтчТопливо!R12C3:R15C3)),ОтчТопливо!R12C9:R15C9,MAX(IF(ОтчТопливо!R12C1:R15C1=(CONCATENATE(RC[-2],"" Гос. Номер "",RC[-1])),ОтчТопливо!R12C9:R15C9)))"
Разница лишь в том, что в первом случае сравнивается с ячейкой
ОтчТопливо!R12C2:R100C2=RC[-2] ,
а во втором случае с функцией СЦЕПИТЬ
ОтчТопливо!R12C1:R15C1=(CONCATENATE(RC[-2],"" Гос. Номер "",RC[-1]))


так вот во втором случае выпадает ошибка:
"Run time error 1004
Нельзя установить свойство FormulaArray класса Range"
 
Цитата
Разница лишь в том, что в первом случае сравнивается с ячейкой
Разница в длине строки 207 и 278 без знака =.
 
gling, и Вы правы, черт возьми  8)
А вообще, спасибо Вам, действительно много дельных советов и подсказок!  :) :idea:
 
ILUHA!
Можно мне вставить свои "5 копеек"?
На мой взгляд...
Поскольку Вы используете Макрос, зачем Вам "геморрой", связанный с использованием формул массива?
Не проще ли, создать тривиальный, "гибкий" макрос, решающий Вашу проблему?  :)
 
Мотя, я с удовольствием оценю Ваши "5 копеек"  ;)
Да, я использую и формулы и макросы. Честно я делаю по мере своих возможностей и нашедшей информации. Поэтому у меня так и получается.
Цитата
Мотя написал: Не проще ли, создать тривиальный, "гибкий" макрос, решающий Вашу проблему?  
Да, скорее всего Вы правы, но я пока не могу ))
 
Цитата
ILUHA написал: но я пока не могу ))
Ну, стало быть, «Лиха беда начало»!
 
Мотя, ну, стало быть, так оно и есть
 
Я думаю, что, если бы поставили вопрос не про ошибку, которая возникает при вставке макросом формулы, а про саму задачу, то давно получили бы решение.
Страницы: 1
Наверх