Unable to set Formula Array property of the range class

[Chippi]

Hi guys,

I need your help. I am doing some data cleaning and I have multiple columns, each with unique formulas. I recorded each column's execution as separate macros.

I'm having trouble while running this macro which I recorded. I get an error saying " Unable to set formulaArray property of the range class". Can somebody tell me whats wrong? or have an alternate code in vba?

Range("P2").Select
    Selection.FormulaArray = _
        "=IF(SUM(LEN(RC[-1])-LEN(SUBSTITUTE(RC[-1],{1,2,3,4,5,6,7,8,9,0},"""")))=8,SUBSTITUTE(TEXT(LEFT(INT(NPV(-0.9,,IFERROR(MID(LEFT(RC[-1],MIN(IFERROR(SEARCH(CHAR(ROW($65:$90)),RC[-1],MIN(FIND({0,1,2,3,4,5,6,7,8,9},RC[-1]&""0123456789"",1))),1000))),1000-COLUMN(R2),1)%,""SNNA""))),9),REPT(""0"",8)),""0000000"",""pN Ns""),SUBSTITUTE(TEXT(LEFT(INT(NPV(-0.9,,IFERROR(MID(LEFT(RC[-1],MIN(IFERROR(SEARCH(CHAR(ROW(R65:R90)),RC[-1],MIN(FIND({0,1,2,3,4,5,6,7,8,9},RC[-1]&""0123456789"",1))),1000))),1000-COLUMN(R2),1)%,""SNNA""))),9),REPT(""0"",7)),""0000000"",""pN Ns""))"
    Selection.AutoFill Destination:=Range("P2:P40000")
    Range("P2:P40000").Select
End Sub

Any help is appreciated.

Thanks,
Chippi

[Richard Buttrey]

Hi,

Would you copy and paste here the formula from the workbook please.