Страницы: 1
RSS
Вставить формулы массива макросом
 
Доброго времени суток, ребята.

На днях писал сюда вопрос, и добрый человек мне помог, за что ему огромное спасибо. предыдущая тема
Но сегодня возникла новая проблема. Мне необходимо к имеющемуся коду макроса добавить вставку формулы в виде массива (имитация нажатия Ctrl+Shift+Enter).
Подскажите, пожалуйста, как это можно сделать. Заранее благодарю. Ниже код макроса.
Код
Private Function IsInArray(stringToBeFound As Variant, arr As Variant) As Boolean
'    Debug.Print "ProductivityClass.IsInArray"
    
    IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function
 
 
 
Sub FillWithFormulas(arr As Variant)
    Dim ar As Range
    Dim rg As Range
    
    With [Database].ListObject
    
        For Each ar In .Range.Offset(-1).Resize(1).SpecialCells(2, 2).Areas
            For Each rg In ar
                If IsInArray(Split(rg.Address, "$")(1), arr) Then
                    With Intersect(rg.EntireColumn, [Database].ListObject.DataBodyRange)
                            .Formula = rg.Value
                            .Formula = .Value
                    End With
                End If
            Next rg
        Next ar
    End With
End Sub

Изменено: Admiral_East_Wind - 24.09.2019 09:28:13
 
Admiral_East_Wind, для начала приведите своё сообщение в порядок: для оформления кода тегом существует специальная кнопка (см. скрин).
 
Код
.FormulaArray=
Соблюдение правил форума не освобождает от модераторского произвола
 
у меня почему-то выпадает следующая ошибка
Цитата
Run-time error '1004':
Unable to set the FormulaArray property of the Range class
При этом длина символов в формуле никак не влияет появление этой ошибки (в интернете пишут, что .Formula.Array позволяет работать с формулами не длиннее 255 символов).  
 
показывайте в файле - у меня всё пашет
Соблюдение правил форума не освобождает от модераторского произвола
 
Возможно, формула на листе записана не в той локализации или не с тем разделителем. Прикладывайте файл - тогда можно будет не гадать.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Я очистил 99% информации, которая содержалась в оригинальном файле, чтобы его загрузить. Поскольку в VBA я сплошной профан, прошу вас предупредить меня, если я удалил что-то лишнее. Вроде, того, что имеется, достаточно для отработки макроса, и проблема с работой также сохранилась.

Благодарю за то, что помогаете!  
 
Вы бы макросы-то там оставили. Как и откуда вызываете процедуру вставки FillWithFormulas?
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
У меня имеются два модуля. Честно сказать, я не в курсе, почему их не видно. Мне часто так говорят, когда я пересылаю файл. Сейчас приложу код обоих.
Код
Sub Analytics()
    Dim calculation_array As Variant: calculation_array = Array("B")
    FillWithFormulas calculation_array
End Sub
Изменено: Admiral_East_Wind - 24.09.2019 10:31:34
 
Код
Private Function IsInArray(stringToBeFound As Variant, arr As Variant) As Boolean
'    Debug.Print "ProductivityClass.IsInArray"
    
    IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

 

Sub FillWithFormulas(arr As Variant)
    Dim ar As Range
    Dim rg As Range
    
    With [Database].ListObject
    
        For Each ar In .Range.Offset(-1).Resize(1).SpecialCells(2, 2).Areas
            For Each rg In ar
               If IsInArray(Split(rg.Address, "$")(1), arr) Then
                    With Intersect(rg.EntireColumn, [Database].ListObject.DataBodyRange)
                            .FormulaArray = rg.Value
                            .Formula = .Value
                    End With
                    
                End If
            Next rg
        Next ar
        
    End With
    
End Sub
 
Цитата
Admiral_East_Wind написал:
Мне часто так говорят, когда я пересылаю файл
а Вы файл сохраняйте в формате "Файл Microsoft Excel с поддержкой макросов - .xlsm" - тогда они будут сохраняться. Или еще вариант: убедитесь, что макросы В ЭТОМ ФАЙЛЕ, а не в личной книге макросов PERSONAL.XLSB(или в надстройке)
Изменено: Дмитрий(The_Prist) Щербаков - 24.09.2019 10:33:31
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
В общем я еще сразу написал:
Цитата
Дмитрий(The_Prist) Щербаков написал:
или не с тем разделителем
в англ.версии формул(и при вставке с использованием Formula) разделитель аргументов - ЗАПЯТАЯ, а не точка-с-запятой. Т.е. в B2 должно быть записано так:
Код
=IF([@[В объеме анализа]]=1,"+","не в объеме анализа")
а вот что Вы там собрались как формулу массива вставлять мне вообще непонятно. Где сам массив-то? И главное - где смысл вставки формулы массива, если формула работает и так и к тому же в итоге заменяется значениями?
И еще главнее: в умных таблицах НЕЛЬЗЯ использовать формулы массива. Это ограничение Excel.
Изменено: Дмитрий(The_Prist) Щербаков - 24.09.2019 10:39:05
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Вы знаете, действительно, макросы хранились в  личной книге макросов. Я сейчас, вроде, перенес в файл.
По поводу массивов. Этот файл упрощенный. Я работаю с файлом, где очень много формул, которые без массивов не сработают. Это действительно нужно, но в пример я не могу перенести оригинальные формулы, потому что они зависят от полей умной таблицы, которые я удалил.
Замена разделителя на запятую, увы, не помогла.  :(
P.S. Я читал про то, что формулы массивов не работают в умных таблицах. Я не знаю, справедливо ли это для VBA, но обычная формула массива в моей версии excel работает прекрасно.  
Изменено: Admiral_East_Wind - 24.09.2019 10:55:55
 
Цитата
Admiral_East_Wind написал:
Замена разделителя на запятую, увы, не помогла
логично. Я же написал, что формула массива не может быть вставлена в умную таблицу
Цитата
Admiral_East_Wind написал:
Я не знаю, справедливо ли это для VBA
неважно откуда вставляется формула массив в умную таблицу - она НЕ МОЖЕТ ТАМ РАБОТАТЬ, это программное ограничение самого Excel.
Поэтому просьба показать где у Вас Ваша формула массива в итоге вставлена и работает.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Извините, что-то не пойму, почему она не может там работать? Вот посмотрите, пожалуйста, разве вот этот макрос, сделанный через запись с экрана, не вставляет формулу массива в умную таблицу?  8-0  
 
Цитата
Admiral_East_Wind написал:
макрос, сделанный через запись с экрана
не вижу там макроса записи с экрана. Все тот же макрос из стартового сообщения, притом без малейшего намека на формулу массива, но с теми же ошибками в виде неверных разделителей в функции.
Нашел - не туда смотрел(уже три Ваших файла наплодил) :). Тогда смотри ответ ниже: в записанном макросе Вы вставляете формулу массива лишь в одну ячейку, а в коде - сразу в несколько.
Так же не вижу в файле хотя бы созданной руками формулы массива внутри умной таблицы.
Да, пометочка про формулы массива в умной таблице(забыл сразу написать): их нельзя использовать вводом сразу в несколько ячеек. Т.е. именно так, как Вы хотите судя по коду. Их можно записывать только в одну отдельную ячейку и распространять на остальные ячейки столбца.
В итоге Вам достаточно сделать так:
Код
.Cells(1, 1).FormulaArray = rg.Value
хотя смысл формулы массива все равно непонятен конкретно для данной формулы...
Изменено: Дмитрий(The_Prist) Щербаков - 24.09.2019 11:39:48
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Спасибо за помощь! Много полезного от Вас узнал.  :)  
 
Добрый день!
У меня похожая проблема, не вставляется формула массива макросом.
Макрос выглядит так:
Код
If Not Intersect(Target, Range("F106")) Is Nothing Then
        If Range("F106").Value = "Есть" Then Range("D37").FormulaArray = "=1+1+1+1"
    End If

Этот макрос работает норм и вставляет формулу массива, которая выдает результат 4. Нормально вставляет  {=1+1+1+}.
НО нужная мне формула в экселе выглядит так:
Код
=ЕСЛИОШИБКА(ЕСЛИ(E116=0;"";ИНДЕКС(BJ6:BK15;ПОИСКПОЗ(МИН(ЕСЛИ(BK6:BK15>E116;BK6:BK15));BK6:BK15;0);1));"")   

Скорей всего саму формулу надо как-то по другому прописать, чтоб VBA её нормально вставлял в эксель, но я не знаю - как именно. Помогите плз!
Изменено: Yuriy575 - 27.03.2020 21:28:16
 
Код
=IFERROR(IF(E116=0,"",INDEX(BJ6:BK15,MATCH(MIN(IF(BK6:BK15>E116,BK6:BK15)),BK6:BK15,0),1)),"")

Это получаем так: http://prntscr.com/rnytte
Изменено: Hugo - 28.03.2020 19:06:27
 
Hugo, выдает ошибку "Нельзя установить свойство FormulaArray класса Range"
Я выяснил методом тыка, что если в формуле заменить кавычки на цифры - то формула вставляется нормально и эта ошибка не выскакивает... Почему так происходит, и как мне всё-так вставить с кавычками, т. к. мне нужно чтобы при определенных условиях формула выдавала "пустоту"

Вот вариант который вставляется нормально. Заменил "" на 777:
Код
=IFERROR(IF(E116=0,777,INDEX(BJ6:BK15,MATCH(MIN(IF(BK6:BK15>E116,BK6:BK15)),BK6:BK15,0),1)),777)

Но как всё-таки вставить с кавычками? Нужно именно чтобы с кавычками вставлялось, никакие другие значения не подходят, иначе дальше у меня вылазят ошибки в расчетах(
Изменено: Yuriy575 - 28.03.2020 19:17:02
 
кАвычки - """"
Вместо одной - две
 
vikttur,поправился)
 
Цитата
Yuriy575 написал: поправился
Не усугубляйте, лишний вес - он всегда лишний :)
 
vikttur,  :D
В общем я задачку решил - просто в формуле прописал несколько лишних условий и отошел от макроса, макрос не понадобился в моем случае.
P. S. Но всё-таки интересно почему VBA ругался на кавычки.
 
Цитата
vikttur написал: Вместо одной - две
 
Я дал синтаксис самой формулы, как её видит сам Эксель. Чтобы её прописать кодом в ячейку - нужно задваивать кавычки, я думал Вы знаете...
Страницы: 1
Наверх