'Unable to set the FormulaArray property of Range class' even though the length is 215

[imkumarvikash]

I am trying to use the following Array Formula in my VBA.

Range(Cells(2, "AF"), Cells(4, Col)).FormulaArray = "=IF(COLUMNS($AF2:AF2)<=$AE2,INDEX('Demand File '!$A$2:$A$3001,SMALL(IF('Demand File '!$CC$2:$CC$3001=CONCAT($J2,$K2,$L2,""Yes""),ROW('Demand File '!$A$2:$A$3001)-ROW('Demand File '!$A$2)+1),COLUMNS($AF2:AF2))),"")"

It's giving me an error - 'Unable to set the FormulaArray property of Range class'

However, the below formula works fine (logic not correct, just testing syntax)

Range(Cells(2, "AF"), Cells(lRow, Col)).FormulaArray = "=IF(COLUMNS($AF2:AF2)<=$AE2,INDEX('Demand File '!$A$2:$A$3001,SMALL(IF('Demand File '!$J$2:$J$3001='Swap List '!$J2,ROW('Demand File '!$A$2:$A$3001)-ROW('Demand File '!$A$2)+1),COLUMNS($AF2:AF2))),"""")"

 

[RoryA]

Welcome to the forum.

Your first formula doesn't have the quotes doubled at the end. It should terminate in ,"""")" rather than just ,"")"

 

[imkumarvikash]

Thanks, Actually you were the reason I joined this forum, was looking at your other answer on using Replace to handle large formula

Welcome to the forum.

Your first formula doesn't have the quotes doubled at the end. It should terminate in ,"""")" rather than just ,"")"

Wow! That solves my one problem.

The next problem is that the formula -

Range(Cells(2, "AF"), Cells(lRow, Col)).FormulaArray = "=IF(COLUMNS($AF2:AF2)<=$AE2,INDEX('Demand File '!$A$2:$A$3001,SMALL(IF('Demand File '!$CC$2:$CC$3001=CONCAT($J2,$K2,$L2,""Yes""),ROW('Demand File '!$A$2:$A$3001)-ROW('Demand File '!$A$2)+1),COLUMNS($AF2:AF2))),"""")"

just copies the code from AF2 to AS1800 (which is currently the lRow and Col) instead of increasing the Row and Column number it along the rows and columns. How can I tackle this?

 

[RoryA]

Assigning the FormulaArray property of a multiple cell range is the equivalent of selecting all those cells manually, typing the formula and then pressing Ctrl+Shift+Enter, which will array enter exactly the same formula into all of the cells. Use Autofill to populate the range after entering the array formula into the first cell only.

 

[imkumarvikash]

Assigning the FormulaArray property of a multiple cell range is the equivalent of selecting all those cells manually, typing the formula and then pressing Ctrl+Shift+Enter, which will array enter exactly the same formula into all of the cells. Use Autofill to populate the range after entering the array formula into the first cell only.

So, I can use Range("AF2:AF" & lRow).FillDown to copy it all the way down
and Range("AF2:" & Col & "2").FillRight to copy the code all the way right

But what about the remaining cells in the matrix.

I can think of using a loop starting from first column to Col and then using FillDown to fill everything, but is there an easier and less awkward way?

 

[RoryA]

Fill right in the first row, then fill down all columns rather than just the first one.

 

[imkumarvikash]

Fill right in the first row, then fill down all columns rather than just the first one.

My problem is that number of Columns is a variable (with the last column as Col)
I cannot manually write a FillDown command for all the columns. That's why I asked if I need to use a Loop

So I didn't get what you just explained.

 

[RoryA]

Use the Fillright as you showed above, then:

Range("AF2:" & Col & lRow).FillDown

 

[imkumarvikash]

Use the Fillright as you showed above, then:

Range("AF2:" & Col & lRow).FillDown

Wow! It worked, I was hesitating to use this logic thinking that I am asking the program to go diagonally down, which FillDown is not supposed to do. But it works, that too faster than the loop.
Thanks a bunch.