Entering Long Array Formulas in VBA

By in Uncategorized on .

Use the FormulaArray property of the Range object to enter array formulas in a cell from VBA. The array formula must be 255 characters or less or you’ll see the ever-helpful “Unable to set the FormulaArray property of the Range class” error.

Dave sent me a solution to this problem that’s shown below. It replaces part of the formula with a place holder, then uses the Replace method of the Range object to add the rest. Apparently Replace doesn’t have such limitations as FormulaArray does.

By the way, he uses JWalk’s cool calendar formula (as seen in the Ugly Formulas comments) as his example. I love that formula, although that doesn’t really have anything to do with this post.

”’ If the long formula can be broken into parts, where the second part can
”’ be replaced by a dummy function, this approach can be used…
Public Sub LongArrayFormula()
    Dim theFormulaPart1 As String
    Dim theFormulaPart2 As String
    theFormulaPart1 = “=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))-“ & _
                          “MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-“ & _
                          “(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+” & _
                          “{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),”“”“,” & _
                          “X_X_X())”
                         
    theFormulaPart2 = “DATE(YEAR(NOW()),MONTH(NOW()),1)-“ & _
                      “(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+” & _
                      “{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)”
       
    With ActiveSheet.Range(“E2:K7”)
        .FormulaArray = theFormulaPart1
        .Replace “X_X_X())”, theFormulaPart2
        .NumberFormat = “mmm dd”
    End With
   
End Sub

Nice work, Dave. Thanks for the suggestion.

102 thoughts on “Entering Long Array Formulas in VBA

  1. David Wasserman

    While I consider myself fairly proficient at Excel in general, I get all weak in the knees when confronted with Array Formulas. Is there anywhere I can find a good tutorial dealing exclusively with creating and using Array Formulas?

    Thank you.

    David

  2. gokhan

    I was wondering whether you guys can help me out with this one

    setup: win2000, excel 2000
    problem:

    i have a long array formula

    X_TABLE_FORMULA = “=SUM(IF(B$1=HISTORIC_DATA!$E$5:$E$1130;IF($A2=HISTORIC_DATA!$C$5:$C$1130;HISTORIC_DATA!$H$5:$H$1130;0)))/SUM(IF(B$1=HISTORIC_DATA!$E$5:$E$1130;IF($A2=HISTORIC_DATA!$C$5:$C$1130;HISTORIC_DATA!$B$5:$B$1130;0)))”

    of course entering it via:

    Selection.FormulaArray = X_TABLE_FORMULA

    returns the well known error message.
    however if i split it down, e.g.

    X_TABLE_FORMULA_1 = “=IF(B$1=HISTORIC_DATA!$E$5:$E$1130;””YO””;””NO””)”

    returns this error. you guys have any idea?
    any comment, suggestion is highly appreciated.

  3. Paul

    Has anyone got a technique for doing this with chart series formulas? I’m trying to delink a chart’s data which I can do by hand because of the 1024 formula limit, but from VBA I’m restricted to 255.

    I can’t use the Replace method as the ChartSeries.Formula property is not a Range object. Any ideas would be really useful, as I’m stumped…

  4. Paul

    Hi Jon,

    Thanks for your reply. I had actually stumbled acorss your site whilst searching for an answer to this question.

    Unfortunately your solution doesn’t work. The problem is that setting the formula property on a chart series with anything over 255 characters causes a failure. WorksheetFunction.Substitute just returns a string and so the problem still exists. The only way I’ve find to do it, is to use the technique that involves saving your chart in the user gallery, and then copying the chart from there.

    Why does Excel (I’m using 97) have a 1024 character limit when a user types a formula in, and a 255 one from within VBA? It seems to be a silly limitation.

  5. Jon Peltier

    The stated limit of 1024 characters for the series formula is misleading, because each component of the formula (name, xvalues, values, and plot order) behaves as if it has a limit of around 255. The practical limit is therefore much lower than 1024.

    Are you trying to use assign a VBA array to a series, or is there a reason you don’t want data in the worksheet? If it’s the former, dump the data into worksheet ranges, and point the series formula at these ranges.

  6. Paul

    Thanks for the speedy reply.

    I’m just trying to delink the data as the chart is created in another workbook. There’s definitely some discrepancy between manually entering a formula and assigning it in VBA as I can hit F9 in the formula bar and it works fine, but by using the same formula from within VBA (or assigning an array, etc) it fails. I’ve also ensured that each section of the formula is less than 255 characters but setting the formula property still fails.

    If possible, I’d rather not have the raw data stored anywhere apart from in the chart. I realise that I can store the data in another (possibly hidden) sheet, but it’s just frustrating when I have this much trouble automating something I can do by hand.

  7. Jon Peltier

    You can F9 the series formula, and it /might/ work (I just ran off a quick example, and it didn’t), but as soon as you touch the formula again, you’ll be swarmed with errors.

    The ultimate delink is copy picture.

  8. Jonathan

    Maybe someone stumbles over this post –
    even if its way in the past…

    I’ve got a problem:
    I need to replace the current year in formulas of many sheets, those formulas are right next to the limit of 1024, as the year is contained in some network-file-name from which to include data;

    So i went about trying to cope with those long strings –
    as an automatic replace won’t work (“Formula too long”).

    In test i’m able to write a formula which is exactly 1024 characters long into a Cell (from VBA),

    BUT – herein lies my real problem – i cannot read it in again from VBA;

    e.g.
    Range(“B5?).Formula = str ‘works, with str containin 1024 long formula

    str2 = Range(“B5?).Formula ‘fails with 1004 – application or objectdefined error

    Anyone got an idea for me?
    -Jonathan

  9. Jonathan

    BTW: reading the formula stops working when setting a formula of length greater than 1019 – 1020 cannot be read….

  10. Stephanie

    I am receiving the Excel message “Formula is too long” – is this formula limitation documented anywhere? How did you come to find that to be the limitation?

  11. Jocelyn Paine

    Dick,

    I hit the same problem with array formulae, and have just tried the “replace” trick. It doesn’t work for me, though (on Office Excel 2003). To test it, I put into the range a very simple formula that is just “X_X_X()”, and then replace that with a long formula that I’ve built in a loop, in variable s. But the replace fails with a “type mismatch” error when s becomes 256 or so long. Here’s the code. Any solutions would be very welcome.

    Sub test()
    Dim s As String
    Dim r As Range

    s = “0?
    For i = 1 To 88
    s = s & “+” & i
    Next i
    MsgBox Len(s)
    Set r = Range(“a1:a10?)

    r.FormulaArray = “=X_X_X()”
    r.Replace “X_X_X()”, s
    End Sub

  12. Charles Williams

    Jocelyn,

    this works
    Sub test()
    Dim s As String
    Dim r As Range

    s = “0?
    For i = 1 To 86
    s = s & “+” & i
    Next i
    MsgBox Len(s)
    Set r = Range(“a1:a10?)

    r.FormulaArray = “=fred1+fred2?
    r.Replace “fred1?, s
    r.Replace “fred2?, s
    End Sub

    Another useful trick is to rename all the worksheets to very short names before inserting the array formulae, then rename them back afterwards.

    You should also be aware that the 256 character transfer limits are not the same in different versions of excel.

  13. Chuck Grob

    Using XCL 2003 VBA, I create strings of dates that are pipe-delimited and paste each of them into one cell (my application requires this). The date format is mm/dd/yyyy. Since the number of dates is a variable and it may be up to 30 or 35 dates, my strings that exceed 255 characters are being truncated. Is there hope for pasting all of my complete strings in my worksheet?

    Thanks for a response.

  14. jkpieterse

    Chuck,

    As long as you use the Value property to write the data to the cells, you should be fine and able to write up to 32,767 charaters worth of dates into a cell.

  15. Helen

    Please help. I’ve tried the same solution but it did not work for me. The X_X_X()) never get replaced. Is there any other way for me to do this.

    Thanks

    ‘Update for sequence array: T120-T136

    theFormulaPart1 = “=IF(ISERROR(INDEX(SGData!$A$1:$L$” & SGDataLastRow _
    & “,SMALL(IF(SGData!$A$1:$A$” & SGDataLastRow _
    & “=F120:G120,ROW(SGData!$A$1:$A$” & SGDataLastRow _
    & “)),ROW(SGData!$1:$” & SGDataLastRow & “)),9)),””””,X_X_X())”

    theFormulaPart2 = “INDEX(SGData!$A$1:$L$” & SGDataLastRow _
    & “,SMALL(IF(SGData!$A$1:$A$” & SGDataLastRow _
    & “=F120:G120,ROW(SGData!$A$1:$A$” & SGDataLastRow _
    & “)),ROW(SGData!$1:$” & SGDataLastRow & “)),9)”

    FormulaString = theFormulaPart1 + theFormulaPart2

    With ActiveSheet.Range(“T120:T136?)
    .FormulaArray = theFormulaPart1
    .Replace “X_X_X())”, theFormulaPart2
    End With

  16. Piyush

    I get the same error but my ArrayFormula is only 121 characters long.
    My formula is:

    =SUM(IF(FREQUENCY(IF(LEN(B4:B150)>0,MATCH(B4:B150,B4:B150,0),””), IF(LEN(B4:B150)>0,MATCH(B4:B150,B4:B150,0),””))>0,1))

    It finds the total no of unique values in a range of duplicating values.

  17. RUSSOOL

    I have been trying to spread the formula below in 2 parts. I have not been able to spread it !!

    “=SUM(IF(‘Jalons et Niv Serv’!$F$1:$F$1000 = $G” & i & “,IF(‘Jalons et Niv Serv’!$Q$1:$Q$1000 “”””,IF(‘Jalons et Niv Serv’!$U$1:$U$1000 = “”X”” ,IF(‘Jalons et Niv Serv’!$Q$1:$Q$1000 Paramètres!$G$7,IF(‘Jalons et Niv Serv’!$Q$1:$Q$1000 Paramètres!$G$8,IF(‘Jalons et Niv Serv’!$Q$1:$Q$1000 Paramètres!$G$9,IF(‘Jalons et Niv Serv’!$Q$1:$Q$1000 Paramètres!$G$11,1*(‘Jalons et Niv Serv’!$Q$1:$Q$1000),0),0),0),0),0),0),0))”

    Could someone please help me.

    Thanks a lot

  18. Max Mulawa

    Hi Dick,

    I was wondering if you tried changing ‘Regional and Language Options’->’Standards and Formats’ on your windows installation to let say German (Switzerland).
    In my case this resulted in ‘funny’ situation when calls to Replace function were just ignored.

    After doing some debugging I’ve come up with rather obscure solution:

    With ActiveSheet.Range(“E2:K7?)
    .FormulaArray = theFormulaPart1
    if .FormulaLocal.IndexOf(“;”) > 0 then
    theFormulaPart2 = theFormulaPart2.Replace(“,”,”;”)
    end if
    .Replace “X_X_X())”, theFormulaPart2
    .NumberFormat = “mmm dd”
    End With

    So, when using Replace function the 2 other formula parts must be changed from commas (,) to semicolons (;) before being passed to Replace function.
    If comma=>semicolon transformation wouldn’t be applied, the Replace function would ignore both operations as they would violate the FormulaArray.

    The code is far from being perfect, but it works at least for the SWISS ;)

  19. Max Mulawa

    Hmm, if in first place I would assign part 1 of the formula array to FormulaLocal, and execute Replace function i’m not getting FormulaArray at the end.

    It seems that Range.Replace function modifies FormulaLocal content rather than FormulaArray.

  20. David McIntosh

    Here is what I’ve found:
    When you try to set the formula (the formulaArray, formula, formulalocal, etc.) via any method, the formula you use must be parsable. It does not need to value, it just needs to be syntactically correct so that Excel can parse it. Then, it seems, each part of the parse tree must be less than 256 characters. Thus, any quoted strings or token names in an expression must be less than 256 characters, though the whole expression can be more than 256 characters. If you try rng.replace(token,replacementString), the replacement string must be less than 256 characters. Repeated application of rng.replace can be used to build up a large formula, but each replacement must have a replacement string of less than 256 characters. Furthermore, the limit of 255 characters on tokens in the parsed formula prevent you from using this strategy:
    rng.arrayFormula = “=””Function(param1,param2,zzzzz”””
    rng.replace “zzzzz”, “param3,param4,zzzzz”
    rng.replace “zzzzz”, “param5,param6,zzzzz”

    rng.replace “zzzzz”, “paramN,paramM)”
    rng.replace “”””, “”
    This would work beautifully (and be a general, generic work-around, so long as the formula has no quotes in it), except that as soon as the string in the formula gets to be >255 characters, the replace function ceases working. One can do this:
    rng.arrayFormula = “=””Function(param1,param2,zzzzz”””
    rng.replace “zzzzz”””, “””&””param3,param4,zzzzz”
    rng.replace “zzzzz”””, “””&””param5,param6,zzzzz”

    rng.replace “zzzzz”””, “””&””paramN,paramM)”
    rng.replace “””&”””, “”
    rng.replace “”””, “”

    but the second-last replacement fails. If I could think of a single replacement command that would strip out the ‘”&”‘ and the leading ” and trailing ” all in one replacement, then this would work as a general solution.

  21. Mala

    What if we needed to to the long formula trick with FormulaArray in R1C1 style? I find that the Replace part is simply ignored…

  22. shg

    I know SendKeys is deprecated, but this also works:

    Public Sub LongArrayFormula()
        Dim theFormula  As String
        theFormula = “=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))-“ & _
                     “MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-“ & _
                     “(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+” & _
                     “{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),”“”“,” & _
                     “DATE(YEAR(NOW()),MONTH(NOW()),1)-“ & _
                     “(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+” & _
                     “{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)”
        With ActiveSheet.Range(“A1:G5”)
            .NumberFormat = “@”
            .Value = theFormula
            .NumberFormat = “mmm dd”
            .Select
            DoEvents
            Application.SendKeys “{F2}^+~”
        End With
    End Sub

    But you can’t step through it unless you want to end up in the Object Browser …

  23. Nick

    Another workaround to this is to define a name first and then use the name. It works, just tested it.

    Here is the code… strLongFormula contains the text of your long formula.

    objWorkbook.Names.Add “LongFormula”, “=” & strLongFormula
    With objDataSheet.Range(objDataSheet.Cells(2, 1), objDataSheet.Cells(200, 1))
    .FormulaArray = “=LongFormula”
    End With

  24. Brian

    Here is the code… strLongFormula contains the text of your long formula.

    objWorkbook.Names.Add “LongFormula”, “=” & strLongFormula
    With objDataSheet.Range(objDataSheet.Cells(2, 1), objDataSheet.Cells(200, 1))
    .FormulaArray = “=LongFormula”
    End With

    –
    sounds great, however I couldn’t get it to work in 2007. VBA doesn’t seem to appreciate objWorkbook,objDataSheet, etc. and no idea how to get it to work with these. I’ve tried using workbook / worksheet objects but it doesn’t like those either. any idea what I’m doing wrong?

  25. Brian

    OK I declared the objects and used Set to assign them as needed in my workbook, however during this statement,

    objWorkbook.Names.Add “LongFormula”, “=” & strLongFormula

    I still get: Run Time Error ‘1004’ – Application-defined or object-defined error

  26. Nick

    Brian,

    You will get ‘Runtime Error 1004’ when there is a problem with the formula itself. When your code paused, go to the VB debug window and type:
    ? strLongFormula
    Hit enter and copy the result to the clipboard. Try to enter that formula manually into Excel using the Formulas>Name Manager tool.

  27. Brian

    Thanks for the feedback. Didn’t find what that error all about, it goes right through the code with no error when I test it now. It writes the formula string in the Name “refers to” field, which seems correct.

    The only hangup is when I write to the cell with the last statement, what I get is {=LongFormula} in the cell, instead of the formula itself.

  28. Brian

    Ahh I think I see now, your algorithm stores the formula in the name permanently, whereas here I was thinking it was trying to writing it back to the cell.

  29. BeRniTo

    Brian, how did you make the replacement happen? It isn’t working for me.
    Could you please post a simple example?

  30. Andrew Kalemkarian

    I’m not entirely sure why, but in implementation I had to add LookAt:=xlPart to my Replace implementation.

    This is not necessary to get the example to work! — but, it fixed my specific implementation.

    I made my Replace look like this:

            .Replace “X_X_X())”, theFormulaPart2, LookAt:=xlPart
  31. Walter

    Hi everybody,

    I am trying to use the same solution that Dave gave but it is not working for me. The Replace does not do anything. This is the code:

    Public Sub LongArrayFormula()
     
        Dim theFormulaPart1 As String
        Dim theFormulaPart2 As String
       
        theFormulaPart1 = “=SUM(IF(FREQUENCY(IF((R4C1:R2000C1”“”“)*(R4C2:R2000C2=R10C2)*(R4C4:R2000C4=R7C2)*” & “X_X_X()” & “,MATCH(R4C1:R2000C1,R4C1:R2000C1,0)),ROW(INDIRECT(““1:”“&ROWS(R4C1:R2000C1))))>0,1,0))”
        theFormulaPart2 = “(IF(R3C4=”“”“,1,R4C6:R2000C6=R3C4))*(IF(R3C6=”“”“,1,R4C10:R2000C10=R3C6))*(IF(R3C9=”“”“,1,R4C5:R2000C5=R3C9))*(IF(R3C12=”“”“,1,R4C13:R2000C13=R3C12)),MATCH(R4C1:R2000C1,R4C1:R2000C1,0)),ROW(INDIRECT(““1:”“&ROWS(R4C1:R2000C1))))>0,1,0))”
       
        With ActiveSheet.Range(“E2:E2”)
            .FormulaArray = theFormulaPart1
            .Replace “X_X_X()”, theFormulaPart2
        End With
       
    End Sub

    I appreciate all your help to resolve this issue.

    Thanks a lot in advance.

    Walter.

  32. Dick Kusleika

    Walter: If I try to do it manually (after converting to A1 style reference) it errors because there are mismatched parentheses. I think that may be the problem, but I’m not sure. Can you record the formula manually and paste it here? Use code tags so you don’t lose the greater-than signs.

  33. Andrew Kalemkarian

    For fun, I wrote function to easily use the alternative Named Formula method to insert a long array formula. It’s convenient (i.e. no need to break apart the formula), but at the end of the day I don’t like this method because the formula does never appears in the formula bar, only the name.

    Public Sub LongArrayFormula(objRng As Range, strFormula As String)
        Dim strName As String
           
        ‘Create a “unique” name
       strName = “LongFormula” & (10 * Rnd + 1) * Format(Now(), “yyyymmddHhNnSs”)
       
        objRng.Worksheet.Names.Add strName, strFormula  ‘assumes formula already begins with =
       objRng.FormulaArray = “=” & strName
    End Sub
  34. Pingback: Toutes versions Formule matricielle longue et VBA

  35. Pingback: Array Formulas in Excel – Using VBA « One Stop Analytics

  36. Basava

    PLs help me on this…

    This is my recorded code for Array formula..
    I could not able run the same because of the lenght of the formula.

    Range(“b14”).Select
    Selection.FormulaArray = _
    “=SUMIFS(All!C[6],All!C2,SUBSTITUTE(R4C2,”” “”,””””),All!C8,SUBSTITUTE(RC2,”” “”,””””))+(SUMIFS(Data!C[6],Data!C2,SUBSTITUTE(R4C2,”” “”,””””),Data!C8,SUBSTITUTE(RC2,”” “”,””””)))-SUM(SUMIFS(Data!C[6],Data!C2,SUBSTITUTE(R4C2,”” “”,””””),Data!C8,SUBSTITUTE(RC2,”” “”,””””),Data!C1,INDEX(OFFSET(All!R3C109,,,COUNTA(All!C109)),)))”

    I have seen your slution for the same kind but i did get this as i’m very new for the VBA…
    I strongly believe you can help on this.

  37. Pingback: Working With Range.FormulaArray In VBA | RAD Excel

  38. AdamT

    I concur with felix. An excellent and very useful article and extremely comprehensive set of comments too. Well done all round!

  39. Jason

    I found this article very helpful, thanks to the original poster, and all the commenters.

    The long array formulas that I’m trying to get into the cells are dynamically generated, and there’s not a natural split point where both sides are parsable. What I was able to exploit however was that my formulas had lots of long string constants in them. I was able to replace these with short placeholders, the expand the long strings back out.

    For example, if the formula was something like
    =someSub(“longstring”, “anotherlongstring”, “morelongstring”)
    this became
    =someSub(“$0”, “$1”, “$2”)
    which is < 255 chars so it can be set, then finally expand the strings again using the Replace technique shown in the article.

    My code (just drafted so can't say it's perfect)

    Public Sub setFormulaArray(cellRange As Range, formula As String)
    ' For short formulas, just do it the simple way
    If Len(formula) <= 255 Then
    cellRange.FormulaArray = formula
    Return
    End If

    ' Create the replacement formula, replacing strings as "$1" placeholders
    Dim replacementArray(25) As String
    replacementArraySize = 0
    shortFormula = ""
    insideQuote = False
    For i = 1 To Len(formula)
    char = Mid(formula, i, 1)
    If (char = """") Then
    If (insideQuote) Then
    insideQuote = False
    replacementArraySize = replacementArraySize + 1
    Else
    insideQuote = True
    shortFormula = shortFormula & """$" & replacementArraySize & """"
    End If
    Else
    If (insideQuote) Then
    replacementArray(replacementArraySize) = replacementArray(replacementArraySize) & char
    Else
    shortFormula = shortFormula & char
    End If
    End If
    Next i

    ' Set the cell's formula to the shortened version, which should now be < 255 chars
    cellRange.FormulaArray = shortFormula

    ' Expand the created placeholders back out
    For i = 0 To replacementArraySize – 1
    cellRange.Replace "$" & i, replacementArray(i)
    Next i
    End Sub

  40. Nick Perkins

    For those situations where you can have a hidden sheet, you can use a copy and paste/pastespecial to get the formula into the cell.

    I wasn’t able to get the above code options to work but did manage to get the copy/paste option going.

  41. Dave Plume

    As Shg suggests above, SendKeys seems to work and was the least painful approach for me. In C#:

    if (formula.Length < 255)
    {
    range.FormulaArray = formula;
    }
    else
    {
    range.Formula = formula;
    range.Select();
    Globals.ThisAddIn.Application.SendKeys("{F2}");
    Globals.ThisAddIn.Application.SendKeys("^+{ENTER}");
    }

    (Globals was created for me by Visual Studio when I created my add-in).

  42. Cheryl

    Does anyone know if this method of replacing the arrayformula also works on Mac Excel 2011?

    I received an error 1004 (application-defined or object-defined error)

  43. Artur

    Hi, Could anyone please tell me why the following code doesn’t work and how I can fix it?

    —–

    Dim p_1, p_2 As String

    For y = 1 To 100

    p_1 = "COVARIANCE.S(IF(Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & "<AVERAGE(Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & ")," & _
    "IF(Bstrap!" & ColumnLetter(10 + y) & 13 & "" & ":" & ColumnLetter(10 + y) & j_end + 12 & "  " & """""" & ", " & _
    "p_2p()))"

    p_2 = "Bstrap!" & ColumnLetter(10 + y) & 13 & "" & ":" & ColumnLetter(10 + y) & j_end + 12 & "))," & _
    "IF(Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & "<AVERAGE(Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & ")," & _
    "IF(Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & "  " & """""" & ", " & _
    "Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & ")))" & _
    "/VAR.S(IF(Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & "<" & _
    "AVERAGE(Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & "), " & _
    "IF(Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & "  " & """""" & "," & _
    "Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & ")))"

    With Cells(12 + y, 4)
       .FormulaArray = p_1
       .Replace "p_2p()))", p_2
    End With

    —–

    When I run this code, the same error appears (“Unable to set the FormulaArray property of the Range class”).

    The funny thing is that when I remove “=” from before “COVARIANCE.S”, VBA does not show any error and it displays the formula correctly (just does not calculate it, since “=” is missing. When I put “=” myself in Excel, the formula performs the calculations correctly.

    Thank you for your time and help!

  44. Artur

    Sorry, I forgot to add “=” before “COVARIANCE.S” in the code and “Next y” at the end.

  45. Chris

    Hello,
    I’ve hit a brick wall with this one, can anyone help me get the following code to work?

    Sub LongArrayFormula1()

    Dim frmla1 As String
    Dim frmla2 As String
    Dim frmla3 As String

    ‘fullfrmla = “=SUM(IF(FREQUENCY(IF(A2:A10000&B2:B10000&C2:C10000″”””,MATCH(“”~””&A2:A10000&B2:B10000&C2:C10000,A2:A10000&B2:B10000&C2:C10000&””””,0)),ROW(A2:A10000)-ROW(A2)+1),1))”

    frmla1 = “=SUM(IF(FREQUENCY(IF(A2:A10000&B2:B10000&C2:C10000″”””,” & _
    “X(“”~””&X,X&””””,0)),ROW(X)-ROW(X)+1),1))”
    frmla2 = “MATCH(“”~””&A2:A10000&B2:B10000&C2:C10000,A2:A10000&B2:B10000&C2:C10000&””””,0)),” & _
    “ROW(X)-ROW(X)+1),1))”
    frmla3 = “ROW(A2:A10000)-ROW(A2)+1),1)”

    With ActiveSheet.Range(“AF2”)
    .FormulaArray = frmla1
    .Replace What:=”X(“”~””&X,X&””””,0)),ROW(X)-ROW(X)+1),1))”, Replacement:=”frmla2″, LookAt:=xlPart
    .Replace What:=”ROW(X)-ROW(X)+1),1))”, Replacement:=”frmla3″, LookAt:=xlPart
    End With

    End Sub

    At the moment it is just displaying the following in cell AF2:
    =SUM(IF(FREQUENCY(IF(A2:A10000&B2:B10000&C2:C10000″”””,X(“”~””&X,X&””””,0)),ROW(X)-ROW(X)+1),1))

    No error, but the replace doesn’t appear to be working.

    I have data in three columns (A,B, and C) and I want the cell AF2 to display the number of unique values when those three cells are concatenated. (A10000 is only used to make sure that all cells are included, as the populated rows will change).

    Thanks.

  46. Dick Kusleika

    Chris: I changed it around a bit, but I can’t get it to work either. Actually, if I step through the code it works. If I just run the code, it doesn’t. I tried putting some .Calculate calls in there as well as some Application.Wait calls to see if I could get it to go, but no dice.

    Sub LongArrayFormula1()

        Dim frmla1 As String
        Dim frmla2 As String
        Dim frmla3 As String
       
        'fullfrmla = "=SUM(IF(FREQUENCY(IF(A2:A10000&B2:B10000&C2:C10000<>"""",MATCH(""~""&A2:A10000&B2:B10000&C2:C10000,A2:A10000&B2:B10000&C2:C10000&"""",0)),ROW(A2:A10000)-ROW(A2)+1),1))"
       
        frmla1 = "=SUM(IF(FREQUENCY(IF(A2:A10000&B2:B10000&C2:C10000<>"""",YYYY),ZZZZ),1))"
       
        frmla2 = "MATCH(""~""&A2:A10000&B2:B10000&C2:C10000,A2:A10000&B2:B10000&C2:C10000&"""",0)"
       
        frmla3 = "ROW(A2:A10000)-ROW(A2)+1"
       
        With ActiveSheet.Range("AF2")
            .FormulaArray = frmla1
            .Replace What:="YYYY", Replacement:=frmla2, LookAt:=xlPart
            .Replace What:="ZZZZ", Replacement:=frmla3, LookAt:=xlPart
        End With

    End Sub
  47. Vicktor Schausberger

    formulaArray, call my attention I didn’t found anything about frequency function as formula array in a macro.
    my concern is I would like to know how to loop the frequency function.
    Range(“Q2:Q54”).Select
    Selection.FormulaArray = “=FREQUENCY(R[1]C[-15]:R[18]C[-10],RC[-1]:R[52]C[-1])”

    Range(“R2:R54”).Select
    Selection.FormulaArray = “=FREQUENCY(R[2]C[-16]:R[19]C[-11],RC[-2]:R[52]C[-2])”

    from here how can I apply the for .. next loop ideas. thanks.

  48. Jack

    In .Replace “X_X_X())”, theFormulaPart2

    What if size of theFormulaPart2 is greater than 255 chars, I am getting a type mismatch error if theFormulaPart2 is greater than 255 chars

  49. Vic

    I worked on this issue for a few hours and was able to make my array formula with the replace function to work properly. I realized that each of the parsed formula should be executable with the correct syntax. You cannot subdivide your long formula to just random divisions. Not sure that I am clear on this comment. Here’s my code that works:

     formula3 = "=IF(B5=""Armouring"","""",Y5*COUNT(IF((($E$2:$K$2=""Mon"")+($E$2:$K$2=""Tue"")+($E$2:$K$2=""Wed"")+($E$2:$K$2=""Thu"")+($E$2:$K$2=""Fri""))*(E5:K5=0),(E5:K5)))+" & _
                            "dummy1())"
        formula4 = "IF(Y5=""12"",SUM(IF((($E$2:$K$2=""Mon"")+($E$2:$K$2=""Tue"")+($E$2:$K$2=""Wed"")+($E$2:$K$2=""Thu"")+($E$2:$K$2=""Fri""))*(E5:K5<12)*(E5:K5>0),Y5-(E5:K5))),dummy2())-W5"
        formula5 = "SUM(IF((($E$2:$K$2=""Mon"")+($E$2:$K$2=""Tue"")+($E$2:$K$2=""Wed"")+($E$2:$K$2=""Thu"")+($E$2:$K$2=""Fri""))*(E5:K5<8)*(E5:K5>0),Y5-(E5:K5))))"
        range("R5").Select
        With ActiveSheet.range("R5")  'ABS Formula
            .FormulaArray = formula3
            .Replace "dummy1()", formula4, LookAt:=xlPart
            .Replace "dummy2())", formula5, LookAt:=xlPart
           
        End With
  50. Pingback: How to activate multiple array formulas at once

  51. Pingback: FormulaArray Error - Unable to set the FormulaArray property of the range class

  52. Allen Copeland, Jr.

    I’ve ran across this very frequently when automating report generation. One of the things I have tried to do to fix this is the following:
    http://www.abstraction-project.com/documents/excel/FunctionCalls.xlsm

    The above link represents a document which constructs the formula in objects by using method calls. Knowing the formula length issue exists, it uses placeholders for each parameter and component of the expression. Binary operands, function parameters, and so on. I’ve only had a chance to test it on Excel 2013.

    Hope it helps someone else, too!

    Originally created it as an answer to a question: http://stackoverflow.com/questions/2320258/is-there-a-workaround-when-hitting-the-maximum-summands-in-sum-in-an-excel-form/25945343#25945343
    Where I go as ‘Alexander Morou’

  53. Pingback: VBA: These blog posts cover hard topics on working with array formulas in Excel | >keen /c /o /d /e

  54. Nilesh

    Guys, I have used below function and it works perfect, however this takes a while to take effect(because of ‘For’ loop)

    Function CreateArrayFunction(ByVal sRange As String, formula As String) As Boolean
    For Each oCell In Worksheets(“PCN IT Data Tables”).Range(sRange)
    oCell.FormulaArray = formula
    Next oCell
    End Function

    Any one knows alternative to this?

    Thanks,
    -N

  55. Nilesh

    Guys, Ihave below function and it works perfect, however it takes a while to take effect(because of for loop)

    Function CreateArrayFunction(ByVal sRange As String, formula As String) As Boolean
        For Each oCell In Worksheets("PCN IT Data Tables").Range(sRange)
            oCell.FormulaArray = formula
        Next oCell
    End Function

    does anyone knows alternative to this?

    Thanks
    -N

  56. Nilesh

    Ok, so 255 character limit is applicable to function posted by me above. I have tried splitting formulas. It gives me error

    “Application-defined or object-defined error”

    any clue on this guys?

  57. Nilesh

    here is my formula:
    “=IF(ISERROR(MEDIAN(IF(WorkSheet!R2C4:R10000C4=R50C2,IF(WorkSheet!R2C7:R10000C7=R51C2,IF(WorkSheet!R2C12:R10000C12=RC1,IF(WorkSheet!R2C18:R10000C18=R2C17,WorkSheet!R2C13:R10000C13)))))),0,MEDIAN(IF(WorkSheet!R2C4:R10000C4=R50C2,IF(WorkSheet!R2C7:R10000C7=R51C2,IF(WorkSheet!R2C12:R10000C12=RC1,IF(WorkSheet!R2C18:R10000C18=R2C17,WorkSheet!R2C13:R10000C13))))))”

  58. Pingback: FormulaArray for formula more than 255 characters

  59. Max

    Hi guys. I have read every single comment in this thread and looked in other websites but I haven’t been able to solve this problem.

    I am doing a macro that uses array formulas and when the macro hits the first array formula, I get an error. Basically I was getting the 1044 error referencing to the 255 character limit on array formulas. So I tried splitting the formula in 2 parts each with less than 255 characters (about 180 each) and still hitting an error. My original formula that you would see in an Excel cell is as follows:

    {=IF(ISERROR(INDEX('Acquisition'!$A$1:$J$242,SMALL(IF('Acquisition'!$C$2:$C$242='CAP Tracking'!$C$1,ROW('Acquisition'!$C$2:$C$242)),ROW(1:1)),4)),"",INDEX('Acquisition'!$A$1:$J$242,SMALL(IF('Acquisition'!$C$2:$C$242='CAP Tracking'!$C$1,ROW('Acquisition!'$C$2:$C$242)),ROW(1:1)),4))}

    Obviously too long. So following this post suggestion, I broke down the code in the following manner:

    Range("B12").Select

    Dim FormulaPart1 As String
    Dim FormulaPart2 As String

       FormulaPart1= "=IF(ISERROR(INDEX('Acquisition'!R1C1:R242C10,SMALL(IF('Acquisition'!R2C3:R242C3='CAP" & _    "Tracking'!R1C3,ROW('Acquisition'!R2C3:R242C3)),ROW(R[-1:1])),4)),""XXX())"
           FormulaPart2= "INDEX('Acquisition'!R1C1:R242C10,SMALL(IF('Acquisition'!R2C3:R242C3='CAP" & _ "Tracking'!R1C3,ROW('Acquisition!R2C3:R242C3)),ROW(R[-11])),4))"

    With ActiveSheet.Range("B12")
         .FormulaArray= FormulaPart1
         .Replace "XXX()", FormulaPart2
    End With

    When I get the error and debug, it always highlights the “.FormulaArray= FormulaPart1” line.

    Any idea how I can fix this problem?? I would greatly appreciate it! I’ve been stuck with this for a whole day!!

    Thank you in advance.

  60. Max

    I apologize, the error I’m getting is the “Run-time error ‘1004’: Unable to set the FormulaArray property of the Range class”

    Just to make it easier to see, I’m breaking up the formula in the “if false” part of the IF function.

    Help please!

  61. Dick Kusleika Post author

    Max: FormulaPart1 has to be a valid formula or you’ll get that error. Here’s how you fix it.

    When you get that error, type ?FormulaPart1 in the Immediate Window. In Excel, go to File – Options – Formulas and change to R1C1. Go to B12 and paste the text from the Immediate Window into the cell and try to enter the formula. It will tell you it’s not valid and tell you that the part that says "XXX() is the culprit.

    After you add the missing closing quotes, you try it again and it says ROW(R[-1:1]) is now the problem. So you get rid the of the colon, I guess, and it works. Make those same changes in your code and it will run. Then switch back to A1 mode.

    FormulaPart1 = "=IF(ISERROR(INDEX('Acquisition'!R1C1:R242C10,SMALL(IF('Acquisition'!R2C3:R242C3='CAPTracking'!R1C3,ROW('Acquisition'!R2C3:R242C3)),ROW(R[-11])),4)),""XXX()"")"
  62. Max

    Thank you Dick! I was able to get past this part of the forumla and I am no longer getting an error here. However I’m having a different issue in this code.

    So after making the right corrections, the code runs without errors and substitutes the “XXX()” how it is supposed to. However, since in FormulaPart1, this last part of the IF function we are writing it “”XXX()””)” which is fine because that way excel detects the XXX() as text and replaces it for FormulaPart2. However, when this replacement gets done, FormulaPart2 comes in surrounded by quotation marks and therefore the formula is not getting executed. I copy the code as I have it now and what the output is once I run the macro:

    Dim FormulaPart1 As String
    Dim FormulaPart2 As String

    FormulaPart1 = “=IF(ISERROR(INDEX(‘Acquisition Branch’!R1C1:R242C10,SMALL(IF(‘Acquisition Branch’!R2C3:R242C3=’Branch CAP Tracking’!R1C3,ROW(‘Acquisition Branch’!R2C3:R242C3)),ROW(R[-11])),4)),””””,””XXX()””)”
    FormulaPart2 = “INDEX(‘Acquisition Branch’!R1C1:R242C10,SMALL(IF(‘Acquisition Branch’!R2C3:R242C3=’Branch CAP Tracking’!R1C3,ROW(‘Acquisition Branch’!R2C3:R242C3)),ROW(R[-11])),4))”
    With ActiveSheet.Range(“B12”)
    .FormulaArray = FormulaPart1
    .Replace “XXX()”, FormulaPart2
    End With

    The outputof this code is:

    {=IF(ISERROR(INDEX(‘Acquisition Branch’!$A$1:$J$242,SMALL(IF(‘Acquisition Branch’!$C$2:$C$242=’Branch CAP Tracking’!$C$1,ROW(‘Acquisition Branch’!$C$2:$C$242)),ROW(1:1)),4)),””,”INDEX(‘Acquisition Branch’!R1C1:R242C10,SMALL(IF(‘Acquisition Branch’!R2C3:R242C3=’Branch CAP Tracking’!R1C3,ROW(‘Acquisition Branch’!R2C3:R242C3)),ROW(R[-11])),4))”)}

    Note how FormulaPart2 comes in FormulaPart1 in between quotation marks > “INDEX(‘Acquisition Branch’!R1C1:R242C10,SMALL(IF(‘Acquisition Branch’!R2C3:R242C3=’Branch CAP Tracking’!R1C3,ROW(‘Acquisition Branch’!R2C3:R242C3)),ROW(R[-11])),4))”

    What this is causing as well is that I end up with the first part of the formula in A1 style while the second part remains in R1C1 style (I’m assuming this is because FormulaPart2 is coming in as text because of the quotation marks, instead of being the IF False clause of the function)

    If I write …,”XXX()”)” in FormulaPart1 instead of …,””XXX()””)”, I get a syntax error, so I tried that.

    If someone knows a way around this I would greatly appreciate the help! It might just be a syntax thing, but I couldn’t figure it out.

    Thank you so much in advance!

    Max

  63. Max

    Thank you Dick, but no luck… When writing the code like that it doesn’t substitute the “XXX()”

    This is how it looks like now.

    Dim FormulaPart1 As String
        Dim FormulaPart2 As String
       
        FormulaPart1 = "=IF(ISERROR(INDEX('Acquisition Branch'!R1C1:R242C10,SMALL(IF('Acquisition Branch'!R2C3:R242C3='Branch CAP Tracking'!R1C3,ROW('Acquisition Branch'!R2C3:R242C3)),ROW(R[-11])),4)),"""",""XXX()"")"
        FormulaPart2 = "INDEX('Acquisition Branch'!R1C1:R242C10,SMALL(IF('Acquisition Branch'!R2C3:R242C3='Branch CAP Tracking'!R1C3,ROW('Acquisition Branch'!R2C3:R242C3)),ROW(R[-11])),4)"
        With ActiveSheet.Range("B12")
            .FormulaArray = FormulaPart1
            .Replace """XXX()""", FormulaPart2
         End With

    And the formula output when I run the macro looks like this:

    {=IF(ISERROR(INDEX('Acquisition Branch'!$A$1:$J$242,SMALL(IF('Acquisition Branch'!$C$2:$C$242='Branch CAP Tracking'!$C$1,ROW('Acquisition Branch'!$C$2:$C$242)),ROW(1:1)),4)),"","XXX()")}

    Any other ideas?

  64. Max

    Nevermind! Changed the cell reference style back to R1C1 style and it works now! Thank you!

    Is there a way to change all of the code I have to R1C1 style not manually? I have part of the code in A1 style and part of it in R1C1 style. My macro is pretty complex and I want to avoid making mistakes.

  65. Dick Kusleika Post author

    Is there a way to change all of the code I have to R1C1 style not manually?

    No. All of my attempts to reliably convert code end in failure. All I can tell you is: before you change anything, write a test. Then make the change and see if the test still works. That’s not a quick solution, but as far as I know, there isn’t a quick one.

  66. Ttes

    Hi All,

    I have a problem. When i record my working formula in macro, the following codes appears.

    ActiveCell.FormulaR1C1 = _
    "=IF(OR(RC8=""Lease Car"",RC9=""Lease Car""),IF(RC8=""Travel Allowance"",SUMIFS(PSA!C22,PSA!C8,RC1,PSA!C16,""=""&"".NL – KM / Mileage""),0)+IF(RC9=""Travel Allowance"",SUMIFS(PSA!C22,PSA!C8,RC1,PSA!C16,""=""&"".NL – KM / Mileage""),0),IF(RC8=""Travel Allowance"",SUMIFS(PSA!C22,PSA!C8,RC1,PSA!C21,""=""&41763,PSA!C16,""=""&"".NL – KM / Mileage""),0)" & _
    """Travel Allowance"",SUMIFS(PSA!C22,PSA!C8,RC1,PSA!C21,"">=""&RC10,PSA!C21,"">=""&41763,PSA!C16,""=""&"".NL – KM / Mileage""),0))"
    Range("K2903").Select

    But when i run it, i encounter 1004 error.

    Would you know if this has sometgint to do with the length of the codes? Thank you in advance!

  67. Emily_jane

    Hello!

    I am trying the way Nick and Brian were talking about (defining a name first and referring to that).

    My code is as follows:

    Dim strLongFormula As String

    strLongFormula = “MAX(IF(‘Quarantine List’!$B$2:$B$” & lastrow2 + 1 & “=’Study Timeline’!A” & lastrow + 1 & “,’Quarantine List’!$D$2:$D$” & lastrow2 + 1 & “))+((MAX(INDIRECT(“”‘Follow Up Appointments’!B””&MATCH(INDEX(‘Quarantine List’!$A$2:$A$” & lastrow2 + 1 & “,MATCH(‘Study Timeline’!A” & lastrow + 1 & “,IF(‘Quarantine List’!$D$2:$D$” & lastrow2 + 1 & “=MAX(IF(‘Quarantine List’!$B$2:$B$” & lastrow2 + 1 & “=’Study Timeline’!A” & lastrow + 1 & “,’Quarantine List’!$D$2:$D$” & lastrow2 + 1 & “)),’Quarantine List’!$B$2:$B$” & lastrow2 + 1 & “),0)),FOLLOW_UP_STUDY,0)&””:G””&MATCH(INDEX(‘Quarantine List’!$A$2:$A$” & lastrow2 + 1 & “,MATCH(‘Study Timeline’!A” & lastrow + 1 & “,IF(‘Quarantine List’!$D$2:$D$” & lastrow2 + 1 & “=MAX(IF(‘Quarantine List’!$B$2:$B$” & lastrow2 + 1 & “=’Study Timeline’!A” & lastrow + 1 & “,’Quarantine List’!$D$2:$D$” & lastrow2 + 1 & “)),’Quarantine List’!$B$2:$B$” & lastrow2 + 1 & “),0)),FOLLOW_UP_STUDY,0)))))”

    ThisWorkbook.Names.Add “LongFormula”, “=” & strLongFormula

    wsTimeline.Activate

    With wsTimeline.Range(“TIMELINE_FOLLOWUP”)(lastrow + 1)

    .FormulaArray = “=LongFormula”

    End With

    The code runs, and the formula appears in the ‘Refers To’ section of the named range.

    The formula in the string also works, I have copied it from the name manager and replaced the cell and it’s all fine.

    However my formula in the required cell is just {=LongFormula}.

    What am I doing wrong?! Please help, I am rather confused!

  68. Dick Kusleika Post author

    @Emily_jane: It won’t work with array formulas. I know Nick said he tested it, but I suspect it was with a formula that worked as a non-array formula. I can’t get it to work with a formula that truly need to be evaluated as an array.

  69. Emily_jane

    Cheers Dick, I’ll try the original way you suggested. Thanks for starting this thread by the way, very useful!

  70. Jörg

    Hello,

    I think that I have a similar problem as Max.
    My original formula that you would see in an Excel cell is as follows:

    {=Concatenate(INDEX(Anschreiben_2015!$C$1:$C$100;Small(IF(Anschreiben_2015!$B$1:$B$100=$B5;ROW($1:$99));1))&","&INDEX(Anschreiben_2015!$C$1:$C$100;Small(IF(Anschreiben_2015!$B$1:$B$100=$B5;ROW($1:$99));2))&","&INDEX(Anschreiben_2015!$C$1:$C$100;Small(IF(Anschreiben_2015!$B$1:$B$100=$B5;ROW($1:$99));3))&","&INDEX(Anschreiben_2015!$C$1:$C$100;Small(IF(Anschreiben_2015!$B$1:$B$100=$B5;ROW($1:$99));4)))}

    My VBA-Code looks like this:

    Dim FormulaPart1 As String
    Dim FormulaPart2 As String

    Range("H41").Select

    FormulaPart1 = "=Concatenate(INDEX(Anschreiben_2015!$C$1:$C$100,Small(IF(Anschreiben_2015!$B$1:$B$100=$B5,ROW($1:$99)),1))&"",""&INDEX(Anschreiben_2015!$C$1:$C$100,Small(IF(Anschreiben_2015!$B$1:$B$100=$B5,ROW($1:$99)),2))&"",""&""xxx()"")"
    FormulaPart2 = "INDEX(Anschreiben_2015!$C$1:$C$100,Small(IF(Anschreiben_2015!$B$1:$B$100=$B5,ROW($1:$99)),3))&"",""&INDEX(INDEX(Anschreiben_2015!$C$1:$C$100,Small(IF(Anschreiben_2015!$B$1:$B$100=$B5,ROW($1:$99)),4))"


    With ActiveSheet.Range("H41")
     .FormulaArray = FormulaPart1
     .Replace """xxx()""", FormulaPart2
    End With

    End Sub

    My result looks like this:

    {=Concatenate(INDEX(Anschreiben_2015!$C$1:$C$100;Small(If(Anschreiben_2015!$B$1:$B$100=$B5;ROW($1:$99));1))&","&INDEX(Anschreiben_2015!$C$1:$C$100;Small(If(Anschreiben_2015!$B$1:$B$100=$B5;ROW($1:$99));2))&","&"xxx()")}

    If I try to change it to R1C1 style the outcome doesnt change.

    Thanks in advance.

  71. Jörg

    I recognized that I had a mistake with a wrong semicolon. My result looks like this now:

    [cc]{=VERKETTEN(INDEX(Anschreiben_2015!$C$1:$C$100;KKLEINSTE(WENN(Anschreiben_2015!$B$1:$B$100=$B5;ZEILE($1:$99));1))&”,”&INDEX(Anschreiben_2015!$C$1:$C$100;KKLEINSTE(WENN(Anschreiben_2015!$B$1:$B$100=$B5;ZEILE($1:$99));2))&”,”&”INDEX(Anschreiben_2015!$C$1:$C$100,Small(IF(Anschreiben_2015!$B$1:$B$100=$B5,ROW($1:$99)),3))&”;”&INDEX(INDEX(Anschreiben_2015!$C$1:$C$100,Small(IF(Anschreiben_2015!$B$1:$B$100=$B5,ROW($1:$99)),4))”)}

    Dont get confused by the formula terms. Its the german translation. But I think thats the reason why it isnt working correctly. It has to be correlated with the use of signs.

  72. Jörg

    Sorry my fault:

    {=VERKETTEN(INDEX(Anschreiben_2015!$C$1:$C$100;KKLEINSTE(WENN(Anschreiben_2015!$B$1:$B$100=$B5;ZEILE($1:$99));1))&”,”&INDEX(Anschreiben_2015!$C$1:$C$100;KKLEINSTE(WENN(Anschreiben_2015!$B$1:$B$100=$B5;ZEILE($1:$99));2))&”,”&”INDEX(Anschreiben_2015!$C$1:$C$100,Small(IF(Anschreiben_2015!$B$1:$B$100=$B5,ROW($1:$99)),3))&”;”&INDEX(INDEX(Anschreiben_2015!$C$1:$C$100,Small(IF(Anschreiben_2015!$B$1:$B$100=$B5,ROW($1:$99)),4))”)}
  73. James

    Hi,

    I am trying to split the below formula into 2 segments to cure my problem but I still receive the run-time error:

    =IF(AB2=0,"NO MATCH",IF(OR(AB2=9,AB2=8),"MATCH, NO BREAK",INDEX(ADV!A:Q,MATCH(1,IF(T2=0,(ADV!C:C=Trades!H2),1)*IF(U2=0,(ADV!G:G=Trades!C2),1)*IF(V2=0,(ADV!F:F=Trades!D2),1)*IF(W2=0,(ADV!D:D=Trades!K2),1)*IF(X2=0,(ADV!A:A=Trades!P2),1)*IF(Y2=0,(ADV!E:E>=SUM(Trades!J2-VLOOKUP(I2,Control!P:R,3,FALSE))),1)*IF(Y2=0,(ADV!E:E<=SUM(Trades!J2+VLOOKUP(I2,Control!P:R,3,FALSE))),1)*IF(Z2=0,(ADV!P:P=Trades!E2),1)*IF(AA2=0,(ADV!K:K=Trades!I2),1),0),17)))

    I have tried just entering the following which is under 255 characters but I still receive an error. Does anyone have any ideas??

    "=IF(AB2=0,""NO MATCH"",IF(OR(AB2=9,AB2=8),""MATCH NO BREAK"",INDEX(ADV!A:Q,MATCH(1,IF(T2=0,(ADV!C:C=Trades!H2),1)*IF(U2=0,(ADV!G:G=Trades!C2),1)*IF(V2=0,(ADV!F:F=Trades!D2),1)*IF(W2=0,(ADV!D:D=Trades!K2),1),0),17)))"
  74. James

    OK, got the first part of the formula working! However I am now having real issues splitting my formula up. Any ideas?

  75. Maha

    Hi, we have a situation where we need split the long array formula Dynamically through vba code. we store the formulas(as string) in a master worksheet. we then loop the rows and set the actual formulas in other worksheets. in this case, with long formula arrays, we have to split the formulas dynamically and replace it through code. all parts is working other than “splitting the formula”. we cant split on every 200 characters as the formula has to be valid. we are struggling here to find how to split a formula which should also be valid.

    any help will be greatly appreciated. Thanks.

  76. Dick Kusleika Post author

    Maha: You could tokenize the formula like here so that you only split where the formula will still work on its own. But that seems like a lot of work. It’s best if a human splits the formula. You say the formulas are listed in a spreadsheet. Can you split it as listed in the spreadsheet and simply combine the components in the code?

  77. Maha

    Hi Dick, Thanks for the suggestion. after days of struggle and googling we decided to split manually.

  78. Pingback: Merging Long FormulaArray in VBA

  79. Pingback: Replace part of formula in ArrayFormula

  80. Zluk

    Thanks a lot. That is the decision!
                Dim f_1 As String
                Dim f_2 As String
                Dim f_3 As String
                Dim f_4 As String
               
                f_1 = "=IFERROR(INDEX(" &amp; MnthRep &amp; "!R8:R300,IF(COUNTIF(R2C1:R300C1,RC1)=1,MATCH(RC1," &amp; MnthRep &amp; "!R8C1:R300C1,0),F_2())," &amp; CStr(total_col) &amp; "),""-"")"
                f_2 = "IF(COUNTIF(R1C1:RC1,RC1)=1,F_3(),F_4())"
                f_3 = "MATCH(1,(" &amp; MnthRep &amp; "!R8C1:R300C1=RC1)*(" &amp; MnthRep &amp; "!R8C" &amp; normacal_col &amp; ":R300C" &amp; normacal_col &amp; "=" &amp; MnthRep &amp; "!R2C9),0)"
                f_4 = "MATCH(1,(" &amp; MnthRep &amp; "!R8C1:R300C1=RC1)*(" &amp; MnthRep &amp; "!R8C" &amp; normacal_col &amp; ":R300C" &amp; normacal_col &amp; "=" &amp; MnthRep &amp; "!R4C9),0)"
             
                With Workbooks(ResWB).Worksheets(r_num).Cells(2, 3 + mnth)
                    .FormulaArray = f_1
                    .Replace "F_2()", f_2
                    .Replace "F_3()", f_3
                    .Replace "F_4()", f_4
                End With
               
                f_1 = "=IFERROR(INDEX(" &amp; MnthRep &amp; "!R8:R300,IF(COUNTIF(R2C1:R300C1,RC1)=1,MATCH(RC1," &amp; MnthRep &amp; "!R8C1:R300C1,0),F_2())," &amp; CStr(norma_col) &amp; "),""-"")"
               
                With Workbooks(ResWB).Worksheets(r_num).Cells(2, 10 + mnth)
                    .FormulaArray = f_1
                    .Replace "F_2()", f_2
                    .Replace "F_3()", f_3
                    .Replace "F_4()", f_4
                End With

  81. Bruno

    Hello! My formula is the following:

    Entire_Formula = “=IF(RC[-3]””261””,IFERROR(IF(INDIRECT(ADDRESS(SUMPRODUCT(–(RC[-4]=R3C30:R100C30),–(R3C32:R100C32″”),ROW(R3C30:R100C30)),33))=””v””,IFERROR(PICalcVal(INDIRECT(ADDRESS(SUMPRODUCT(–(RC[-4]=R3C30:R100C30),–(R3C32:R100C32″”),ROW(R3C30:R100C30)),32)),R[“ & -a & “]C[-3],R[“ & -a & “]C,””total””, 24, 0,””p28509””),””””),IFERROR(PICalcVal(INDIRECT(ADDRESS(SUMPRODUCT(–(RC[-4]=R3C30:R100C30),–(R3C32:R100C32″”),ROW(R3C30:R100C30)),32)),R[“ & -a & “]C[-3],R[“ & -a & “]C,””total””, 24, 0,””p28509””),””””)/RC[2]),””””),IFERROR(IF(INDIRECT(ADDRESS(SUMPRODUCT(–(RC[-4]=R3C30:R100C30),–(R3C32:R100C32″”),ROW(R3C30:R100C30)),33))=””v””,IFERROR(PICalcVal(INDIRECT(ADDRESS(SUMPRODUCT(–(RC[-4]=R3C30:R100C30),–(R3C32:R100C32″”),ROW(R3C30:R100C30)),32)),R[“ & -a & “]C[-3],R[“ & -a & “]C,””total””, 24, 0,””p28509””),””””), IFERROR(PICalcVal(INDIRECT(ADDRESS(SUMPRODUCT(–(RC[-4]=R3C30:R100C30),–(R3C32:R100C32″”),ROW(R3C30:R100C30)),32)),R[“ & -a & “]C[-3],R[“ & -a & “]C,””total””, 24, 0,””p28509””),””””)/RC[2]),””””)- IFERROR(PICalcVal(INDIRECT(ADDRESS(SUMPRODUCT(–(RC[-4]= DIESEL!R3C30:R100C30),ROW(DIESEL!R3C30:R100C30)),32,1,1,””DIESEL””)),R[“ & -a & “]C[-3],R[“ & -a & “]C,””total””, 24, 0,””p28509””),””””))”

    How would you split it to make it works in a formulaarray statement?

  82. Pingback: Stuck on inserting long formula using VB

  83. jiri

    Hello Dick
    I have an array formula in VBA which successfully returns the desired result into the spreadsheet cell without any run-time errors or similar, but when I go into that cell, the whole function with all its replacements mandatorily defined in the VBA code is not there. What is there is just ={IF(ITOTDR1!I22=””,theFormulaPart1,ITOTDR1!I22)}
    Otherwise it works as far as the final result, but another strange thing is that if I manually go into that cell and then go out without confirming the curly brackets by Ctrl+Shift+Enter, the result is the same as with the curly brackets – array formula – where I would have thought that due to the nature of this function it must always be an array formula otherwise the result will not be correct.
    This is the code:
    Sub COMBINETOOL_Rectangle4_Click()
    Dim theFormulaPart1 As String
    Dim theFormulaPart2 As String
    Dim theFormulaPartsub_a As String
    Dim theFormulaPartsub_b As String
    Dim theFormulaPartsub_c As String
    Dim theFormulaPartsub_d As String
    Dim theFormulaPartsub_e As String

    theFormulaPart0 = "=IF(ITOTDR1!R[1]C="""",X,ITOTDR1!R[1]C)"
    theFormulaPart1 = "=IF(x_a,ITOTDR2!RC[-3],IF(x_b,ITOTDR2!RC[-2],IF(x_c,ITOTDR2!RC[-1],IF(x_d,ITOTDR2!RC,IF(x_e,ITOTDR2!RC[1])))))"
    theFormulaPartsub_a = "=AND(ISERROR(MATCH(TRUE,ITOTDR1!R[1]C[-3]:R[1]C=ITOTDR2!RC[-3],0)),ITOTDR2!RC[-3]&lt;&gt;"""")"
    theFormulaPartsub_b = "=AND(ISERROR(MATCH(TRUE,ITOTDR1!R[1]C[-3]:R[1]C=ITOTDR2!RC[-2],0)),ITOTDR2!RC[-2]&lt;&gt;"""")"
    theFormulaPartsub_c = "=AND(ISERROR(MATCH(TRUE,ITOTDR1!R[1]C[-3]:R[1]C=ITOTDR2!RC[-1],0)),ITOTDR2!RC[-1]&lt;&gt;"""")"
    theFormulaPartsub_d = "=AND(ISERROR(MATCH(TRUE,ITOTDR1!R[1]C[-3]:R[1]C=ITOTDR2!RC,0)),ITOTDR2!RC&lt;&gt;"""")"
    theFormulaPartsub_e = "=AND(ISERROR(MATCH(TRUE,ITOTDR1!R[1]C[-3]:R[1]C=ITOTDR2!RC[1],0)),ITOTDR2!RC[1]&lt;&gt;"""")"

    Range("I21").FormulaArray = theFormulaPart0
    Range("I21").Replace What:="X", Replacement:="theFormulaPart1"
    Range("I21").Replace What:="x_a", Replacement:="theFormulaPartsub_a"
    Range("I21").Replace What:="x_b", Replacement:="theFormulaPartsub_b"
    Range("I21").Replace What:="x_c", Replacement:="theFormulaPartsub_c"
    Range("I21").Replace What:="x_d", Replacement:="theFormulaPartsub_d"
    Range("I21").Replace What:="x_e", Replacement:="theFormulaPartsub_e"

    End Sub

    Is it normal or can the code be easily adjusted somehow for the full nested array function to appear in the spreadsheet cell?

    Thank you really a lot,
    jiri

  84. Dick Kusleika Post author

    Jiri

    Change your Replacement arguments to exclude the double quotes. ex:

    
    
    Replacement:=theFormulaPart1
  85. jiri

    Dick – thanks but there`s still apparently something wrong, what I am now getting is #NAME? error.
    Do you think I could send you the spreadsheet with the macro? I have cut all the other stuff and left just the problematic part with the formula so that visibility of what I need to get fixed should be clear. thankyou a lot, jiri

  86. Dick Kusleika Post author

    @jiri

    It turns out that you can’t use R1C1 in a replace unless you’re using R1C1 in Excel. When I changed your replacement text to A1 notation, it worked without error. I rewrote it a little

    Option Explicit
    
    Sub Rectangle2_Click()
    
        Dim aReplace(0 To 6) As String
        Dim i As Long
        Dim vaWhat As Variant
        
        aReplace(0) = "=IF(ITOTDR1!H22=""-"",y_main,ITOTDR1!H22)"
        aReplace(1) = "IF(y_a,ITOTDR2!F21,IF(y_b,ITOTDR2!G21,IF(y_c,ITOTDR2!H21,IF(y_d,ITOTDR2!I21,IF(y_e,ITOTDR2!J21,"""")))))"
        aReplace(2) = "AND(ISERROR(MATCH(TRUE,ITOTDR1!F22:I22=ITOTDR2!F21,0)),ITOTDR2!F21<>"""")"
        aReplace(3) = "AND(ISERROR(MATCH(TRUE,ITOTDR1!F22:I22=ITOTDR2!G21,0)),ITOTDR2!G21<>"""")"
        aReplace(4) = "AND(ISERROR(MATCH(TRUE,ITOTDR1!F22:I22=ITOTDR2!H21,0)),ITOTDR2!H21<>"""")"
        aReplace(5) = "AND(ISERROR(MATCH(TRUE,ITOTDR1!F22:I22=ITOTDR2!I21,0)),ITOTDR2!I21<>"""")"
        aReplace(6) = "AND(ISERROR(MATCH(TRUE,ITOTDR1!F22:I22=ITOTDR2!J21)),ITOTDR2!J21<>"""")"
        
        vaWhat = Split("x y_main y_a y_b y_c y_d y_e", Space(1))
        
        With Range("H21")
            .FormulaArray = aReplace(0)
            For i = 1 To 6
                .Replace vaWhat(i), aReplace(i), xlPart
            Next i
        End With
    
        aReplace(0) = "=IF(ITOTDR1!I22=""-"",y_main,ITOTDR1!I22)"
        aReplace(1) = "IF(y_a,ITOTDR2!F21,IF(y_b,ITOTDR2!G21,IF(y_c,ITOTDR2!H21,IF(y_d,ITOTDR2!I21,IF(y_e,ITOTDR2!J21,"""")))))"
        aReplace(2) = "AND(ISERROR(MATCH(TRUE,ITOTDR1!F22:I22=ITOTDR2!F21,0)),H21<>ITOTDR2!F21,ITOTDR2!F21<>"""")"
        aReplace(3) = "AND(ISERROR(MATCH(TRUE,ITOTDR1!F22:I22=ITOTDR2!G21,0)),H21<>ITOTDR2!G21,ITOTDR2!G21<>"""")"
        aReplace(4) = "AND(ISERROR(MATCH(TRUE,ITOTDR1!F22:I22=ITOTDR2!H21,0)),H21<>ITOTDR2!H21,ITOTDR2!H21<>"""")"
        aReplace(5) = "AND(ISERROR(MATCH(TRUE,ITOTDR1!F22:I22=ITOTDR2!I21,0)),H21<>ITOTDR2!I21,ITOTDR2!I21<>"""")"
        aReplace(6) = "AND(ISERROR(MATCH(TRUE,ITOTDR1!F22:I22=ITOTDR2!J21)),H21<>ITOTDR2!J21,ITOTDR2!J21<>"""")"
    
        With Range("I21")
            .FormulaArray = aReplace(0)
            For i = 1 To 6
                .Replace vaWhat(i), aReplace(i), xlPart
            Next i
        End With
    
    End Sub
  87. Gerard

    Hi,

    I am also stuck, using arrayformulas with vba. This is a part of my code:

        Sub Formulas_Afwezigheid_Verlof()

        'Declare variables
        Dim RepYear As Integer
        Dim RepMonth As Integer
        Dim MonthColumn As String
        Dim p, f, s, fsn As String
        Dim ArrFormula As String
        Dim col, row As Integer

        'Set variable values
        RepYear = Sheets("Parameters").Range("B4").Value
        RepMonth = Sheets("Parameters").Range("B6").Value
        p = Sheets("Parameters").Range("B8").Value &amp; "Data\CrewurenAnalyse\" &amp; RepYear &amp; "\"

        'Activate sheet
        Sheets("Afwezigheid VD ZD SU").Select

        'Loop through months &lt; reporting month
        For col = 1 To RepMonth
                If col &lt; 10 Then
                    MonthColumn = "0" &amp; col
                Else
                    MonthColumn = col
                End If
                f = "Crewurenanalyse van 01-" &amp; MonthColumn &amp; "-" &amp; RepYear &amp; " - " &amp; WorksheetFunction.Text(DateSerial(RepYear, MonthColumn + 1, 0), "DD-MM-YYYY") &amp; ".xlsm"
                s = "Crewurenanalyse van 01-" &amp; MonthColumn &amp; "-" &amp; RepYear
                fsn = "'" &amp; p &amp; "[" &amp; f &amp; "]" &amp; s &amp; "'"
                ArrFormula = "=INDEX(" &amp; fsn &amp; "!$N:$N;MATCH(""Percentage""&amp;"""";" &amp; fsn &amp; "!$A:$A&amp;" &amp; fsn &amp; "!$B:$B;0))"
                MsgBox (ArrFormula)
                ActiveSheet.Cells(2, 1 + col).FormulaArray = ArrFormula
        Next col

    End Sub

    This code results in the run-time error ‘1004’: Unable to set the FormulaArray property of the Range class.

    When I check the content of the ArrFormula variable it is:
    =INDEX(‘D:\UMC Managementreporting\2017\Data\CrewurenAnalyse\2016[Crewurenanalyse van 01-01-2016 – 31-01-2016.xlsm]Crewurenanalyse van 01-01-2016′!$N:$N;MATCH(“Percentage”&””;’D:\UMC Managementreporting\2017\Data\CrewurenAnalyse\2016[Crewurenanalyse van 01-01-2016 – 31-01-2016.xlsm]Crewurenanalyse van 01-01-2016′!$A:$A&’D:\UMC Managementreporting\2017\Data\CrewurenAnalyse\2016[Crewurenanalyse van 01-01-2016 – 31-01-2016.xlsm]Crewurenanalyse van 01-01-2016’!$B:$B;0))

    When I copy-paste this manually in the worksheet (with ++ it works just fine.

    Can someone point me in the right direction?

    Thanks very much in advance.

    Regards,
    Gerard

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax