Unable to set the formulaarray property of the range class

[MrNameless]

Hi Gurus,

I'm new in VBA and I do need some help for the following codes.
In Excel file, my code works fine but not in VBA.

In Excel file, my codes are as follows:

=IF(E2="","Not Found", IF(COUNTIFS(E:E,E2,F:F,F2)>1, IF(COUNTIFS(E$1:E2,E2,F$1:F2,F2)=1, SUMIFS('Traffic Report'!$L:$L,'Traffic Report'!$J:$J,"*"&F2&"*",'Traffic Report'!$K:$K,"*"&E2&"*"),"Duplicate"), IF(COUNTIFS('Traffic Report'!$J:$J,"*"&F2&"*", 'Traffic Report'!$K:$K,"*"&E2&"*")>0, SUMIFS('Traffic Report'!$L:$L,'Traffic Report'!$J:$J,"*"&F2&"*",'Traffic Report'!$K:$K,"*"&E2&"*"), "Not Found")))

When its converted into VBA, it became

Range("W2").Select
Selection.FormulaArray = _
"=IF(RC[-18]="""",""Not Found"", IF(COUNTIFS(C[-18],RC[-18],C[-17],RC[-17])>1, IF(COUNTIFS(R1C[-18]:RC[-18],RC[-18],R1C[-17]:RC[-17],RC[-17])=1, SUMIFS('Traffic Report'!C12,'Traffic Report'!C10,""*""&RC[-17]&""*"",'Traffic Report'!C11,""*""&RC[-18]&""*""),""Duplicate""), IF(COUNTIFS('Traffic Report'!C10,""*""&RC[-17]&""*"", 'Traffic Report'!C11,""*""&RC[-18]&""*"")>0" & _
", SUMIFS('Traffic Report'!C12,'Traffic Report'!C10,""*""&RC[-17]&""*"",'Traffic Report'!C11,""*""&RC[-18]&""*""), ""Not Found"")))" & _
""

I'm having error as my code is unable to set the formulaarray property of the range class.
Please help..

 

[RoryA]

Welcome to the forum.

I can't see why that would need to be an array formula, so you could just try this:

Range("W2").FormulaR1C1 = _
"=IF(RC[-18]="""",""Not Found"", IF(COUNTIFS(C[-18],RC[-18],C[-17],RC[-17])>1, IF(COUNTIFS(R1C[-18]:RC[-18],RC[-18],R1C[-17]:RC[-17],RC[-17])=1, SUMIFS('Traffic Report'!C12,'Traffic Report'!C10,""*""&RC[-17]&""*"",'Traffic Report'!C11,""*""&RC[-18]&""*""),""Duplicate""), IF(COUNTIFS('Traffic Report'!C10,""*""&RC[-17]&""*"", 'Traffic Report'!C11,""*""&RC[-18]&""*"")>0" & _
", SUMIFS('Traffic Report'!C12,'Traffic Report'!C10,""*""&RC[-17]&""*"",'Traffic Report'!C11,""*""&RC[-18]&""*""), ""Not Found"")))"

 

[MrNameless]

Thank you for your help Rory!

 

[RoryA]

Glad to help.