VBA insert big array formula - problem with replaces

Question

Recently I came across an Excel limitation and learned that we can't write an array formula with more than 255 characters long directly on a cell using VBA. The solution for this problem as I found on the web (including on SO: here), is replacing parts of the formula once it's inserted on the cell.

Here's what I'm trying to do:

Sub WriteFormulaArray()
    '------
        Dim SH As Worksheet: Set SH = ThisWorkbook.ActiveSheet
    '------
        Dim StrF1 As String: StrF1 = "=IFERROR(""PART2""/""PART3"",""PART4"")"
        Dim StrF2 As String: StrF2 = "SUMPRODUCT(IF(('01 - Histórico'!R5C6:R101641C6>=RC1-1)*('01 - Histórico'!R5C6:R101641C6<RC1+1)*('01 - Histórico'!R5C8:R101641C8>=R4C-0.125)*('01 - Histórico'!R5C8:R101641C8<R4C+0.125),'01 - Histórico'!R5C5:R101641C5),'01 - Histórico'!R5C10:R101641C10)"
        Dim StrF3 As String: StrF3 = "SUM(IF(('01 - Histórico'!R5C6:R101641C6>=RC1-1)*('01 - Histórico'!R5C6:R101641C6<RC1+1)*('01 - Histórico'!R5C8:R101641C8>=R4C-0.125)*('01 - Histórico'!R5C8:R101641C8<R4C+0.125),'01 - Histórico'!R5C5:R101641C5))"
        Dim StrF4 As String: StrF4 = "MIN(IF(('01 - Histórico'!R5C6:R101641C6>=RC1-1)*('01 - Histórico'!R5C6:R101641C6<RC1+1),'01 - Histórico'!R5C10:R101641C10))"
    '------
        SH.Cells(5, 2).FormulaArray = StrF1
        SH.Cells(5, 2).Replace """PART2""", StrF2, LookAt:=xlPart
        SH.Cells(5, 2).Replace """PART3""", StrF3, LookAt:=xlPart
        SH.Cells(5, 2).Replace """PART4""", StrF4, LookAt:=xlPart
    '------
End Sub

Well, the previous code is very hard to analyse because of the length and complexity of the formulas, but the routine doesn't do any of the replacements and writes just

{=IFERROR("PART2"/"PART3","PART4")}

on cell B5 (and returns correctly "PART4").

Then I tried to simplify the code to find the problem:

Sub WriteFormulaArrayTest()
    '------
        Dim SH As Worksheet: Set SH = ThisWorkbook.ActiveSheet
    '------
        SH.Cells(6, 2).FormulaArray = "=""TEST1"""
        SH.Cells(6, 2).Replace """TEST1""", """TEST2""", LookAt:=xlPart
    '------
End Sub

And it works just fine. But when I write:

Sub WriteFormulaArrayTest()
    '------
        Dim SH As Worksheet: Set SH = ThisWorkbook.ActiveSheet
    '------
        SH.Cells(6, 2).FormulaArray = "=""TEST1"""
        SH.Cells(6, 2).Replace """TEST1""", "SUM(R5C6:R5C7)", LookAt:=xlPart
    '------
End Sub

the replace doesn't work again and I have the feeling that's the same problem of the first code.

Please note that the routine doesn't give any errors. It just simply doesn't do the replacements.

What am I missing here?? Thanks.

The code executes, it does not arises any errors, but it does nothing, and formulas don't work. I had a similar issue in the past. Please, check https://stackoverflow.com/questions/51196290/error-1004-when-trying-to-use-formulaarray-replace-trick-does-not-work — Foxfire And Burns And Burns, May 29 '19 at 08:54
@FoxfireAndBurnsAndBurns that worked! Thank you a lot! If you post your solution as an answer I'll accept it! — Pspl, May 29 '19 at 08:58
I tried on my Sheet .... Using `"TEST1"` this worked instead of `"""TEST1"""`. — Mikku, May 29 '19 at 09:02
Glad it worked, but it's not my answer, this question is a duplicate. :) — Foxfire And Burns And Burns, May 29 '19 at 09:05
Yep! I guess I didn't do my research properly. Thanks anyway! — Pspl, May 29 '19 at 13:52

VBA insert big array formula - problem with replaces

0 Answers0