Excel 2010 Range.FormulaArray с использованием Range.замещать

Question

Я пытаюсь автоматизировать некоторую ежедневную работу, где я использую формулу массива INDEX()+MATCH(), чтобы собрать некоторые данные из разных рабочих книг в одну «основную» рабочую книгу. Вручную это работает как задумано, но теперь я хочу автоматизировать этот процесс.

Я достиг предела в 255 символов диапазона.Свойство FormulaArray, и пытается обойти его, используя трюк, представленный здесь, но не повезло.

При запуске этого кода часть p1 формулы вставляется в диапазон, но в 2.Замены не выполняются.

Я пробовал разные варианты части "Что".Замените функцию, и «X_X_X()», и «{0}», и многие другие, не повезло.

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
Dim p1, p2, p3 As String
p1 = "=IF(G2<>"""",INDEX('C:\path\to\[document.xlsx]Sheet1'!$P:$P,MATCH(1,({0})*({1}),0)),"""")"
p2 = "B2='C:\path\to\[document.xlsx]Sheet1'!$B:$B"
p3 = "C2='C:\path\to\[document.xlsx]Sheet1'!$C:$C"

With ws.Range("R2")
    .FormulaArray = "=IF(G2<>"""",INDEX('C:\path\to\[document.xlsx]Sheet1'!$P:$P,MATCH(1,({0})*({1}),0)),"""")"
    .Replace "{0}", p2
    .Replace "{1}", p3
End With

Формула, которую я вставляю вручную:

=IF(G2<>"";INDEX('C:\path\to\[document.xlsx]Sheet1'!$P:$P;MATCH(1;(B2='C:\path\to\[document.xlsx]Sheet1'!$B:$B)*(C2='C:\path\to\[document.xlsx]Sheet1'!$C:$C);0));"")

Answer 1

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

Answer 2

Попробуйте сначала ввести формулу в обычном режиме, а затем преобразовать ее в формулу массива, как в листе:

Sub qwerty()
    ActiveCell.Formula = "=MAX(IF(A2>B1:B12,B1:B12))"
    With Application
        .SendKeys "{F2}" ' get into edit mode
        .SendKeys "^+~" '  use CNTRL + Shift = Enter
    End With
End Sub

EDIT # 1:

Этот макрос следует запускать из окна Excel, а не из окна VBE .