VBA Formula Array
Results 1 to 4 of 4

Thread: VBA Formula Array
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Formula Array

    Hello,

    I am trying to input below formula array into a cell M11, it returns me an error massage "Unable to set the FormulaArray property of the Range class":

    lastrow_AFGR2 = Sheets("AFGR").Range("D:D").Find(what:="*", searchdirection:=xlPrevious).Row

    Range("M11").FormulaArray = "=if(len(AFGR!RC[-1])<5,INDEX('Invoice (2)'!R1C4:R" & lastrow_invoice & "C4,MATCH(AFGR!RC[-9]&AFGR!RC[-7]&AFGR!RC[-1]&TRUE,'Invoice (2)'!R1C2:R" & lastrow_invoice & "C2&'Invoice (2)'!R1C18:R" & lastrow_invoice & "C18&'Invoice (2)'!R1C6:R" & lastrow_invoice & "C6 & 'Invoice (2)'!R1C24:R" & lastrow_invoice & "C24,0),1),INDEX('Invoice (2)'!R1C4:R" & lastrow_invoice & "C4,MATCH(AFGR!RC[-9]&AFGR!RC[-7]&AFGR!RC[-1]&TRUE,'Invoice (2)'!R1C2:R" & lastrow_invoice & "C2&'Invoice (2)'!R1C18:R" & lastrow_invoice & "C18&'Invoice (2)'!R1C7:R" & lastrow_invoice & "C7 & 'Invoice (2)'!R1C24:R" & lastrow_invoice & "C24,0),1))"

    I don't want VBA to calculate the output but instead, input this formula so whenever the user change any of the criteria, the output will automatically change.
    Thanks.

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,831
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA Formula Array

    The FormulaArray property has a 255 character limit. However, we can workaround this limit by first entering the formula in the cell with temporary placeholders for parts of the formula, while ensuring that it doesn't exceed 255 characters. Then we can replace those placeholders with their corresponding part of the formula. Note, though, this method requires the use of the A1 reference style, instead of R1C1. So, for this example, let's keep the formula simple. Let's assume we want to enter the following formula (while it's not an array formula, and doesn't exceed 255 characters, it works just the same)...

    Code:
        =IF(L11<5,INDEX($A$1:$A$10,MATCH($M$10,$B$1:$B$10,0)),INDEX($D$1:$D$100,MATCH($M$10,$E$1:$E$1,0)))
    So we can replace each INDEX/MATCH part of the formula with placeholders. For example, the first INDEX/MATCH can be replaced with X_X_X, and the second INDEX/MATCH with Y_Y_Y. Note that there's nothing special about these placeholders, other than we want to make sure that it's a unique string. So the initial string that gets entered into the cell would be as follows...

    Code:
    "=IF(L11<5,X_X_X,Y_Y_Y)"
    Then, we could go ahead and replace each placeholder with their corresponding INDEX/MATCH. So the code would be as follows...


    Code:
        Dim formulaPart1 As String
        formulaPart1 = "INDEX($A$1:$A$10,MATCH($M$10,$B$1:$B$10,0))"
        
        Dim formulaPart2 As String
        formulaPart2 = "INDEX($D$1:$D$100,MATCH($M$10,$E$1:$E$1,0))"
    
    
        With Range("M11")
            .FormulaArray = "=IF(L11<5,X_X_X,Y_Y_Y)"
            .Replace "X_X_X", formulaPart1
            .Replace "Y_Y_Y", formulaPart2
        End With
    Hope this helps!

  3. #3
    New Member
    Join Date
    Mar 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Formula Array

    Thanks. The reason to use array formula is I need to match few criteria to find the corresponding outcome. Any chance to replace by another array formula? Can formulaPart1 and formulaPart2 be array formula?

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,831
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA Formula Array

    Use your array formula instead, and try to adapt the code accordingly. I only used that formula for demonstration purposes.
    Last edited by Domenic; Aug 22nd, 2019 at 09:32 PM.

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
  •