3

I need an array to be inserted into a specific cell and I keep running into the 1004 error. Here is the code:

Range("o37").FormulaArray = "=CONCATENATE(SUM(IF(('2016 Summary'!$T$4:$T$39<=" & mon & ")*" & _
    "('2016 Summary'!$D$4:$D$39>0)*('2016 Summary'!$T$4:$T$39+'2016 Summary'!$D$4:$D$39>" & mon & ")*" & _
    "('2016 Summary'!$S$4:$S$39=TRUE),(1),IF(('2016 Summary'!$T$4:$T$39<=" & mon & ")*('2016 Summary'!$S$4:$S$39=TRUE)*" & _
    "('2016 Summary'!$D$4:$D$39=0),(1),0)))&"" - Employees"")"

That array is supposed to be inserted into O37 with the "mon" variable based on the activecell column. The formula works when I manually put it in the sheet. I have tried splitting it with .replace and I get the same error. What can I do to make this work with formulaArray?

fundesign
  • 33
  • 1
  • 4

2 Answers2

2

Depending upon how long the value in the mon variable is, it seems that your array formula is only marginally over the limit at ~290-310 characters and a large portion of that is comprised of the external worksheet name (e.g. '2016 Summary'). Changing the worksheet name temporarily to a single character is sufficient to bring the formula down to the ~190-210 character range; more than adequate to stuff the formula into the cell's Range.FormulaArray property.

    Dim strWS As String, chrWS As String

    strWS = "2016 Summary"
    chrWS = Chr(167)    '<~~ any unque, legal, single character that can be temporarily used as a worksheet name

    With Worksheets(strWS)
        .Name = Chr(167)
    End With

    With Worksheets("worksheet_with_array_formula")
        .Range("o37").FormulaArray = "=CONCATENATE(SUM(IF((" & chrWS & "!$T$4:$T$39<=" & mon & ")*" & _
            "(" & chrWS & "!$D$4:$D$39>0)*(" & chrWS & "!$T$4:$T$39+" & chrWS & "!$D$4:$D$39>" & mon & ")*" & _
            "(" & chrWS & "!$S$4:$S$39=TRUE),(1),IF((" & chrWS & "!$T$4:$T$39<=" & mon & ")*(" & chrWS & "!$S$4:$S$39=TRUE)*" & _
            "(" & chrWS & "!$D$4:$D$39=0),(1),0)))&"" - Employees"")"
    End With

    With Worksheets(chrWS)
        .Name = strWS
    End With

Excel will change the worksheet name within the formula, adding wrapping ticks (e.g. '2016 Summary'!$T$4:$T$39) to compensate for the space in the new (old) worksheet name.

If you provided more detail on the value of mon and some sample data from the '2016 Summary' worksheet, it also possible that the formula could be improved. At first glance, it looks like an array formula may not be absolutely necessary at all.

  • That works! Thank you so much for your response. Mon is only the column number from activecell.column. – fundesign Jan 27 '16 at 02:27
  • Admittedly, this is a bit of a 'hack' or 'band-aid' approach. However, since the character expansion of a formula can very often be attributed to the necessary addition of long worksheet names, it should be considered as an alternative to defined names or 'partnered formulas' containing portions of the full array formula. –  Jan 27 '16 at 02:58
1
Sub SetTooLongArrayFormula(ByVal rn As Range, ByVal sFormula As String)
    Dim sFormat As String
    sFormat = rn.Cells(1, 1).NumberFormat
    rn.FormulaArray = ""
    rn.Cells(1, 1).NumberFormat = "@"
    rn.Value = sFormula
    rn.Cells(1, 1).NumberFormat = sFormat
    rn.Select
    DoEvents
    SendKeys "{F2}", True
    DoEvents
    SendKeys "+^{ENTER}", True
    
End Sub
Sub Test()
    'Do not run this macro from VBE Editor
    'Run this macro from the Macros ribbon on the Developer tab with the Excel worksheet in front.
    Dim sFormula As String
    sFormula = "=""1"""
    For i = 1 To 250
        sFormula = sFormula & "&""1"""
    Next
    SetTooLongArrayFormula Range("A1:A2"), sFormula
End Sub
yamashiro.akihito
  • 109
  • 6