VBA Formula Array

[HiSan]

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.

 

[Domenic]

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)...

    =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...

"=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...

    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!

 

[HiSan]

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?

 

[Domenic]

Use your array formula instead, and try to adapt the code accordingly. I only used that formula for demonstration purposes.