На днях писал сюда вопрос, и добрый человек мне помог, за что ему огромное спасибо. предыдущая тема Но сегодня возникла новая проблема. Мне необходимо к имеющемуся коду макроса добавить вставку формулы в виде массива (имитация нажатия 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
Run-time error '1004': Unable to set the FormulaArray property of the Range class
При этом длина символов в формуле никак не влияет появление этой ошибки (в интернете пишут, что .Formula.Array позволяет работать с формулами не длиннее 255 символов).
Я очистил 99% информации, которая содержалась в оригинальном файле, чтобы его загрузить. Поскольку в VBA я сплошной профан, прошу вас предупредить меня, если я удалил что-то лишнее. Вроде, того, что имеется, достаточно для отработки макроса, и проблема с работой также сохранилась.
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(или в надстройке)
в англ.версии формул(и при вставке с использованием Formula) разделитель аргументов - ЗАПЯТАЯ, а не точка-с-запятой. Т.е. в B2 должно быть записано так:
Код
=IF([@[В объеме анализа]]=1,"+","не в объеме анализа")
а вот что Вы там собрались как формулу массива вставлять мне вообще непонятно. Где сам массив-то? И главное - где смысл вставки формулы массива, если формула работает и так и к тому же в итоге заменяется значениями? И еще главнее: в умных таблицах НЕЛЬЗЯ использовать формулы массива. Это ограничение Excel.
Вы знаете, действительно, макросы хранились в личной книге макросов. Я сейчас, вроде, перенес в файл. По поводу массивов. Этот файл упрощенный. Я работаю с файлом, где очень много формул, которые без массивов не сработают. Это действительно нужно, но в пример я не могу перенести оригинальные формулы, потому что они зависят от полей умной таблицы, которые я удалил. Замена разделителя на запятую, увы, не помогла. P.S. Я читал про то, что формулы массивов не работают в умных таблицах. Я не знаю, справедливо ли это для VBA, но обычная формула массива в моей версии excel работает прекрасно.
неважно откуда вставляется формула массив в умную таблицу - она НЕ МОЖЕТ ТАМ РАБОТАТЬ, это программное ограничение самого Excel. Поэтому просьба показать где у Вас Ваша формула массива в итоге вставлена и работает.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Извините, что-то не пойму, почему она не может там работать? Вот посмотрите, пожалуйста, разве вот этот макрос, сделанный через запись с экрана, не вставляет формулу массива в умную таблицу?
не вижу там макроса записи с экрана. Все тот же макрос из стартового сообщения, притом без малейшего намека на формулу массива, но с теми же ошибками в виде неверных разделителей в функции. Нашел - не туда смотрел(уже три Ваших файла наплодил) . Тогда смотри ответ ниже: в записанном макросе Вы вставляете формулу массива лишь в одну ячейку, а в коде - сразу в несколько. Так же не вижу в файле хотя бы созданной руками формулы массива внутри умной таблицы. Да, пометочка про формулы массива в умной таблице(забыл сразу написать): их нельзя использовать вводом сразу в несколько ячеек. Т.е. именно так, как Вы хотите судя по коду. Их можно записывать только в одну отдельную ячейку и распространять на остальные ячейки столбца. В итоге Вам достаточно сделать так:
Код
.Cells(1, 1).FormulaArray = rg.Value
хотя смысл формулы массива все равно непонятен конкретно для данной формулы...
Добрый день! У меня похожая проблема, не вставляется формула массива макросом. Макрос выглядит так:
Код
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+}. НО нужная мне формула в экселе выглядит так:
Hugo, выдает ошибку "Нельзя установить свойство FormulaArray класса Range" Я выяснил методом тыка, что если в формуле заменить кавычки на цифры - то формула вставляется нормально и эта ошибка не выскакивает... Почему так происходит, и как мне всё-так вставить с кавычками, т. к. мне нужно чтобы при определенных условиях формула выдавала "пустоту"
Вот вариант который вставляется нормально. Заменил "" на 777:
Но как всё-таки вставить с кавычками? Нужно именно чтобы с кавычками вставлялось, никакие другие значения не подходят, иначе дальше у меня вылазят ошибки в расчетах(
vikttur, В общем я задачку решил - просто в формуле прописал несколько лишних условий и отошел от макроса, макрос не понадобился в моем случае. P. S. Но всё-таки интересно почему VBA ругался на кавычки.