Ошибка VBA 1004 - невозможно установить свойство FormulaArray класса Range

Я вставляю следующую формулу INDEX MATCH в набор ячеек, используя VBA.

        cell2.FormulaArray = _
"=INDEX('[08 Debt Comparison & Provision Report.xlsx]Details by Bus Area &  Location'!AK:AK, _ 
    MATCH(1,('[08 Debt Comparison & Provision Report.xlsx]Details by Bus Area &  Location'!$A:$A = A " & value & ")* _
('[08 Debt Comparison & Provision Report.xlsx]Details by Bus Area &  Location'!$B:$B=""Total""),0))*1000"

При этом я сталкиваюсь с ошибкой 1004 - "Невозможно установить свойство FormulaArray класса Range"

Я вполне уверен, что это как-то связано со ссылкой на рабочую книгу вне текущей, так как она прекрасно работает, когда я ИНДЕКСИРУЕМ МАТЧ внутри той же книги.

Сокращенный вариант формулы (для ясности чтения) следующий:

=INDEX(Sheet2!AK:AK, MATCH(1,(Sheet2!A:A = A5)*(Sheet2!B:B="Total"),0))

Тогда в VBA -

cell2.FormulaArray = "=INDEX(Sheet2!C:C, MATCH(1,(Sheet2!A:A = A5)*(Sheet2!B:B=""Total""),0))"
Источник

2 ответа

Решение

Согласно моему комментарию, это сводится к ограничению длины формулы, которую вы можете использовать в VBA с FormulaArray, Здесь вы, вероятно, можете просто использовать версию без массива:

cell2.Formula = _
"=LOOKUP(2,1/('[08 Debt Comparison & Provision Report.xlsx]Details by Bus Area &  Location'!$A:$A=A" & Value & _
")*('[08 Debt Comparison & Provision Report.xlsx]Details by Bus Area &  Location'!$B:$B=""Total"")," & _
"'[08 Debt Comparison & Provision Report.xlsx]Details by Bus Area &  Location'!AK:AK)*1000"

хотя это вернет последний соответствующий элемент, а не первый, если у вас есть несколько строк, соответствующих вашим критериям.

Источник

Как войти FormulaArray более 255 символов, использующих VBA

Похоже, что в этом случае была альтернативная стандартная формула, которая соответствовала требованиям оригинала FormulaArray, Однако могут быть случаи, для которых нет альтернативной формулы. Для этих случаев у меня есть следующий метод ввода FormulaArray с более чем 255 символов, использующих VBA.

Большую часть времени, когда FormulaArray длиннее 255 символов из-за длины references он содержит, как они могут относиться к длинным постоянным массивам, внешние рабочие книги с большими именами (как в этом случае) или рабочие таблицы с большими именами (также в этом случае). Метод заключается в замене этих длинных строк на более короткие, однако для FormulaArray (после замены) быть принятым как FormulaArray эти короткие строки должны также представлять действительные references,

Согласно вышеизложенному, может быть, по крайней мере, три ситуации с references:

  1. Длинные постоянные массивы: в этих случаях используйте Defined Names как описано здесь

https://support.office.com/en-za/article/Guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

  1. Workbooks с большими именами и
  2. Worksheets с большими именами

Для случаев 2 и 3 применяется тот же метод: использование коротких references указывая на временный Worksheet как временная замена.

Применение метода к этому случаю:

оригинал FormulaArray: использовать переменную sFmlArray держать формулу

Dim sFmlArray As String
sFmlArray = "=INDEX('[08 Debt Comparison & Provision Report.xlsx]Details by Bus Area &  Location'!AK:AK," & _
    "MATCH(1,('[08 Debt Comparison & Provision Report.xlsx]Details by Bus Area &  Location'!$A:$A = A" & bVal & ") * " & _
    "('[08 Debt Comparison & Provision Report.xlsx]Details by Bus Area &  Location'!$B:$B=""Total""),0)) * 1000"

Я предлагаю использовать переменные для хранения имен рабочей книги и рабочего листа, чтобы избежать необходимости записывать их несколько раз.

Dim sFmlRng as string
sFmlRng = "'[08 Debt Comparison & Provision Report.xlsx]Details by Bus Area &  Location'!"

Замените названия рабочей книги и листа в FormulaArray с соответствующей переменной:

sFmlAry = "=INDEX(" & sFmlRng & "AK:AK," & _
    "MATCH(1,(" & sFmlRng & "$A:$A = A" & bVal & ") * " & _
    "(" & sFmlRng & "$B:$B=""Total""),0)) * 1000"

Предполагая, что мы хотим войти так долго FormulaArray В диапазоне D7:D10 давайте присвоим его переменной

Dim rFmlAry as Range
Set rFmlAry = ActiveSheet.Range("D7:D10")

Используйте функцию ниже, чтобы добавить временную таблицу. Эта функция также обеспечивает временный reference использоваться в качестве замены в FormulaArray

Function WshTmp_Add(rFmlAry As Range, sFmlRngTmp As String) As Worksheet
    sFmlRngTmp = "@Tmp"
    With rFmlAry.Worksheet.Parent
        On Error Resume Next
        .Worksheets(sFmlRngTmp).Delete
        On Error GoTo 0
        Set WshTmp_Add = .Worksheets.Add(Before:=.Worksheets(1))
    End With
    WshTmp_Add.Name = sFmlRngTmp
    WshTmp_Add.Tab.Color = 255
    sFmlRngTmp = "'" & sFmlRngTmp & "'!"
    Application.Goto rFmlAry
End Function

Заменить в FormulaArray Длинные ссылки с коротким и введите временный FormulaArray в rFmlAry спектр

sFmlAryTmp = WorksheetFunction.Substitute(sFmlAry, sFmlRng, sFmlRngTmp)
rFmlAry.FormulaArray = sFmlAryTmp

С FormulaArray на месте, заменить временную короткую references с оригинальными длинными

rFmlAry.Replace What:=sFmlRngTmp, Replacement:=sFmlRng, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False

Окончательно удали временный Worksheet

Это вся процедура (в качестве теста добавлено несколько строк в конце для проверки результата)

Sub FormulaArray_Over255Chr()
Dim rFmlAry As Range, sFmlAry As String, bVal As Byte

Dim WshTmp As Worksheet, sFmlAryTmp As String
Dim sFmlRng As String, sFmlRngTmp As String
Dim blAppDisplayAlerts As Boolean
    blAppDisplayAlerts = Application.DisplayAlerts

    Rem Set Ranges & Values
    bVal = 5
    Set rFmlAry = ActiveSheet.Range("D2:D5")

    Rem Define External Reference Variable
    sFmlRng = "'[08 Debt Comparison & Provision Report.xlsx]Details by Bus Area &  Location'!"

    Rem Define FormulaArray (Original) - with References as Variables
    sFmlAry = "=INDEX(" & sFmlRng & "AK:AK," & _
        "MATCH(1,(" & sFmlRng & "$A:$A = A" & bVal & ") * " & _
        "(" & sFmlRng & "$B:$B=""Total""),0)) * 1000"

    Rem Set Range to Enter FormulaArray
    Set rFmlAry = ActiveSheet.Range("D7:D10")

    Rem Add Temporary Worksheet
    Application.DisplayAlerts = False
    Set WshTmp = WshTmp_Add(rFmlAry, sFmlRngTmp)

    Rem Set Temporary FormulaArray - Replace long references
    sFmlAryTmp = WorksheetFunction.Substitute(sFmlAry, sFmlRng, sFmlRngTmp)

    Rem Enter Temporary FormulaArray
    rFmlAry.FormulaArray = sFmlAryTmp

    Rem Set FormulaArray (Original) - Replace short references in situ
    rFmlAry.Replace What:=sFmlRngTmp, Replacement:=sFmlRng, _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False

    Rem Delete Temporary Worksheet
    WshTmp.Delete
    Application.DisplayAlerts = blAppDisplayAlerts

' ****************************************************************
' Lines for TESTING - Resulting FormulaArray - REMOVED when final
' ****************************************************************
    Rem Validate FormulaArray
    Debug.Print String(3, vbLf)
    Debug.Print "FormulaArray in Range: "
    Debug.Print rFmlAry.Cells(1).FormulaArray
    Debug.Print "FormulaArray VBA: "
    Debug.Print sFmlAry
    If rFmlAry.Cells(1).FormulaArray = sFmlAry Then
        MsgBox "FormulaArray with +255 entered successfully" & vbLf & _
            vbLf & rFmlAry.Cells(1).FormulaArray
    Else
        MsgBox "Something did not worked!" & vbLf & _
            vbLf & "Review formulas printed in the Immediate Window"
            SendKeys "^g": Stop
    End If
' ****************************************************************

End Sub
Источник
Другие вопросы по тегам