Excel 2010 Range.FormulaArray usando Range.Replace

Question

Estou tentando automatizar alguns trabalhos diários em que uso uma fórmula de matriz INDEX () + MATCH () para pegar alguns dados de pastas de trabalho diferentes em uma pasta de trabalho 'principal'. Manualmente isso funciona como pretendido, mas agora quero automatizar esse processo.

Eu atingi o limite de 255 caracteres da propriedade Range.FormulaArray e estou tentando contorná-lo usando o truque fornecido aqui , mas sem sorte.

Ao executar este código, a parte p1 da fórmula é inserida no intervalo, mas os 2 .Replace não são executados.

Eu tentei variantes diferentes da parte "What" da função .Replace, tanto "X_X_X ()" quanto "{0}" e muitas outras sem sorte.

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

A fórmula que estou colando manualmente é:

=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

Consegui resolvê-lo primeiro definindo nomes para os intervalos nas pastas de trabalho externas na pasta de trabalho 'principal'. Ao fazer isso, estou mantendo a fórmula abaixo do limite de 255 caracteres de .FormulaArray. Uma boa noite de sono e olhos frescos sobre o problema foi o suficiente: -)

    

Answer 2

Primeiro, insira a fórmula normalmente e, em seguida, converta-a em uma fórmula de matriz, como faria na planilha:

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:

Esta macro deve ser executada a partir da janela Excel em vez da janela VBE .