Unable to set the FormulaArray property of the Range Class

[nniedzielski]

Looking to put this formula into a cell, but get the Run-time Error '1004': Unable to set the FormulaArray property of the Range Class, is there a solution to get Excel to accept this formula?

Dim lr As Long, sh As Worksheet, cad1 As String, cad2 As String, cad3 As String, i As Long
    Set sh = Sheets("Yard")
    lr = sh.Range("A" & Rows.Count).End(xlUp).Row
    cad1 = "'" & sh.Name & "'!" & sh.Range("$C$2:$C$" & lr).Address
    cad2 = "'" & sh.Name & "'!" & sh.Range("$A$2:$A$" & lr).Address
    cad3 = "'" & sh.Name & "'!" & sh.Range("$A$2:$C$" & lr).Address
    For i = 2 To Range("B" & Rows.Count).End(xlUp).Row
      Range("D" & i).FormulaArray = _
        Replace(Replace(Replace("=IFERROR(INDEX(#,SUMPRODUCT((IF(--ISNUMBER(FIND(@,B" & i & "))," & _
        "LEN(@))=(MAX(IF(--ISNUMBER(FIND(@,B" & i & ")),LEN(@)))))*(ROW(@)))-1)," & _
        "VLOOKUP(""*""&B" & i & "&""*"",|,3,0))", "#", cad1), "@", cad2), "|", cad3)
    Next

thank you!

 

[StephenCrump]

You're probably over the 255 character limit for .FormulaArray. (But you'll also get the same message if the worksheet is protected).

You are sort of on the right track, using .Replace, but you need to do it in two steps:
- Put an abridged formula into Excel (<=255 characters)
- Make the text replacement(s) to the formula in Excel.

Have a look here, for example: Entering Long Array Formulas in VBA – Daily Dose of Excel