Error:Unable to Set The FormulaArray Property of the Range Class

[mlo356]

Hi All,

I know this topic has been covered in great length but was not able to find anything that resolves my issue. I have a piece of code that is a FormulaArray but I am receiving the infamous error:

"Unable to Set The FormulaArray Property of the Range Class"

When I switch it from FormulaArray to FormulaR1C1, it will at least enter the code but it doesn’t evaluate properly because it needs to be an array formula. Here is the VB code:

Range("AO2").FormulaArray = "=IF(AND(RC2=""ACTIVITY RECEIVED"",IFERROR(SMALL(IF(ISNUMBER(SEARCH(Key_Word_Tbl,Detail!RC[-10])),ROW(Key_Word_Tbl)),1)-1,0)>0),INDIRECT(""Logic!""&ADDRESS(IFERROR(SMALL(IF(ISNUMBER(SEARCH(Key_Word_Tbl,Detail!RC[-10])),ROW(Key_Word_Tbl)),1)-1,0),4)),""NO"")"

Here is the formula in A1 Notation

=IF(AND($B2="ACTIVITY RECEIVED",IFERROR(SMALL(IF(ISNUMBER(SEARCH(Key_Word_Tbl[KEY WORD EXCEPTIONS],Detail!AE2)),ROW(Key_Word_Tbl[KEY WORD EXCEPTIONS])),1)-1,0)>0),INDIRECT("Logic!"&ADDRESS(IFERROR(SMALL(IF(ISNUMBER(SEARCH(Key_Word_Tbl[KEY WORD EXCEPTIONS],Detail!AE2)),ROW(Key_Word_Tbl[KEY WORD EXCEPTIONS])),1)-1,0),4)),"NO")

Anyone have any ideas as to why I can't code this as a FormulaArray? I am probably overlooking something. Any help would be appreciated.

Thanks!!

 

[NARAYANK991]

Hi ,

Entering a formula using VBA is possible only if the formula length does not exceed 255 characters.

Your posted formula is more than that.

Try the following code to enter the formula :

Sub ArrayFormula()
    With ActiveSheet.Range("AO2")
        s = "=IF(AND(RC2=""ACTIVITY RECEIVED"",IFERROR(SMALL(IF(ISNUMBER(SEARCH(Key_Word_Tbl[KEY WORD EXCEPTIONS],Detail!RC[-10])),ROW(Key_Word_Tbl[KEY WORD EXCEPTIONS])),1)-1,0)>0),YYYY,""NO"")"
   
        Application.ReferenceStyle = xlR1C1


        .FormulaArray = s
   
        .Replace "YYYY", "INDIRECT(""Logic!""&ADDRESS(IFERROR(SMALL(IF(ISNUMBER(SEARCH(Key_Word_Tbl[KEY WORD EXCEPTIONS],Detail!RC[-10])),ROW(Key_Word_Tbl[KEY WORD EXCEPTIONS])),1)-1,0),4))", xlPart
   
        Application.ReferenceStyle = xlA1
    End With
End Sub

 

[mlo356]

Hi ,

Entering a formula using VBA is possible only if the formula length does not exceed 255 characters.

Your posted formula is more than that.

Try the following code to enter the formula :

Sub ArrayFormula()
    With ActiveSheet.Range("AO2")
        s = "=IF(AND(RC2=""ACTIVITY RECEIVED"",IFERROR(SMALL(IF(ISNUMBER(SEARCH(Key_Word_Tbl[KEY WORD EXCEPTIONS],Detail!RC[-10])),ROW(Key_Word_Tbl[KEY WORD EXCEPTIONS])),1)-1,0)>0),YYYY,""NO"")"
   
        Application.ReferenceStyle = xlR1C1


        .FormulaArray = s
   
        .Replace "YYYY", "INDIRECT(""Logic!""&ADDRESS(IFERROR(SMALL(IF(ISNUMBER(SEARCH(Key_Word_Tbl[KEY WORD EXCEPTIONS],Detail!RC[-10])),ROW(Key_Word_Tbl[KEY WORD EXCEPTIONS])),1)-1,0),4))", xlPart
   
        Application.ReferenceStyle = xlA1
    End With
End Sub

Hi NARAYANK991,

This worked perfect. Thank You!