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!

## Like this thread? Share it with others