Set array formula
Results 1 to 5 of 5

Thread: Set array formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,043
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Set array formula

    I have manually created an array formula which returns the correct results.

    I need to adjust the range, so recorded a macro.

    When I tried to run the macro, I got an error:

    Code:
    Unable to set the FormulaArray property of the Range class
    Could it be the formula is too long and complicated for VBA?

    This is the formula:

    Code:
    Selection.FormulaArray = _
            "=IFERROR(IF(RC[-27]=""London"",MATCH(1,(VALUE(RIGHT(LondonData!R8C3:R286C3,9))=RC[-22])*(Start!RC[-2]=LondonData!R8C8:R286C8)*(Start!RC[-26]>LondonData!R8C1:R286C1),0),MATCH(1,(RC[-22]=Paris!R8C3:R29553C3)*(Start!RC[-2]=Paris!R8C4:R29553C4)*(Start!RC[-26]>Paris!R8C1:R29553C1),0)),""Missing"")"
    
    




  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,602
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Set array formula

    You can’t enter an array formula longer than 255 characters via VBA in a single go. At http://www.dailydoseofexcel.com/arch...rmulas-in-vba/, **** Kusleika shows how a formula might be divided it into parts and entered piecemeal, entering the first part, and then the balance via substitution. Here's the example:

    Code:
    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
    The problem is that the formula must be syntactically correct both initially and following each substitution. Here’s an alternative using SendKeys. The last argument in the function call must agree with the reference style of the formula passed to the function.
    Code:
    Sub demo()
      Const sFrm   As String = _
         "=IF(AND(RC[-12]:RC[-1]=""""),""No RoB assessments performed""," & vbLf & _
         "IF(OR(CHOOSE({1,2,3,4,5}, RC[-10], RC[-8],RC[-6], RC[-4], RC[-2])=""no""),""HIGH""," & vbLf & _
         "IF(OR(CHOOSE({1,2,3,4,5}, RC[-10], RC[-8],RC[-6], RC[-4], RC[-2])=""High Risk""),""HIGH""," & vbLf & _
         "IF(OR(CHOOSE({1,2,3,4,5}, RC[-10], RC[-8],RC[-6], RC[-4], RC[-2])=""UNCLEAR""),""UNCLEAR""," & vbLf & _
         "IF(OR(CHOOSE({1,2,3,4,5}, RC[-10], RC[-8],RC[-6], RC[-4], RC[-2])=""Unclear Risk""),""UNCLEAR"",""LOW"")))))"
    
      Debug.Print InsertArrayFormula(Range("A1"), sFrm, xlR1C1)
    End Sub
    
    Function InsertArrayFormula(r As Range, _
                                ByVal sFrm As String, _
                                iRef As XlReferenceStyle, _
                                Optional ByVal sFmt As String = "") As Boolean
      ' shg 2009, 2012, 2016
    
      ' Inserts array formula sFrm into (the first area of) range r.
      ' iRef specifies whether sFrm contains an R1C1- or A1-style formula
      ' sFmt contains an optional format for r. If omitted, the format is unchanged
    
      ' >>>> NB: The VBE CANNOT have focus when this runs, <<<<
      ' >>>> or you'll end up in the Object Browser!       <<<<
    
      Dim iRefSav       As XlReferenceStyle   ' current ref style
      Dim rSel          As Range              ' current selection
    
      If r.Worksheet.ProtectContents Then Exit Function
    
      Set rSel = ActiveWindow.RangeSelection
    
      With Application
        iRefSav = .ReferenceStyle
        .ReferenceStyle = iRef
        If iRef = xlA1 Then sFrm = Application.ConvertFormula(Formula:=sFrm, _
                                                              FromReferenceStyle:=xlA1, _
                                                              ToReferenceStyle:=xlR1C1, _
                                                              RelativeTo:=r.Cells(1))
    
        On Error GoTo Oops
        .ScreenUpdating = False
    
        With r.Areas(1)
          ' Can't put an array formula in cells that are
          ' not either all locked or all unlocked, so ...
          .Locked = .Cells(1).Locked
    
          ' Cache the number format, set to text, insert formula, restore format
          If Len(sFmt) = 0 Then sFmt = .NumberFormat
          .NumberFormat = "@"
          .Value = sFrm
          .NumberFormat = sFmt
    
          Application.Goto .Cells
        End With
    
        DoEvents
        .SendKeys "{F2}^+~"
        DoEvents
        r.EntireRow.AutoFit
        .Goto rSel
        InsertArrayFormula = True
    
    Outtahere:
        .ReferenceStyle = iRefSav
        .ScreenUpdating = True
        Exit Function
      End With
    
    Oops:
      Resume Outtahere
    End Function
    The tricky part about the routine above is that the VBE window cannot have focus when it runs, otherwise you end up in the Object Browser.

    SendKeys is widely regarded as unreliable—I’ve never had a problem with it, but use it so infrequently that that doesn't say much.

  3. #3
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,302
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Set array formula

    Hi,

    You could test Evaluate() ...

    Hope this will help

  4. #4
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,043
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Set array formula

    Thanks for the suggestions.

    I'll try after the weekend and get back with my findings.

  5. #5
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,043
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Set array formula

    I have decided to break down my long array formula into several columns to ease comprehension and also make it VBA compliant.

    Thanks to all for your helpful suggestions.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •