format formula in macro if(or(countif

[Slick55]

Hi,

If I enter this formula in Excel cell, I can get correct return "YES" based on the values from ListSheet C2:C99.

=IF(OR(COUNTIF(K53,"*"&ListSheet!$C$2:$C$99&"*")), "YES", "")

I tried to put this in Macro, I got an error for unexpected end statement on line#1. What is my correct format?

Worksheets(currentSheet).Range("L2").FormulaArray = "=IF( OR( COUNTIF(RC[-1],""*""&ListSheet!$C$2:$C$99&""*"") ) , "YES", "")"
Worksheets(currentSheet).Range("L2:L" & lastRow_Cell).FillDown

Thanks.

[#2 · Accepted solution]

@Slick55 

You should double the quotes inside the string consistently, and you shouldn't mix R1C1 and A1 notation.

Worksheets(currentSheet).Range("L2").FormulaArray = "=IF(OR(COUNTIF(RC[-1],""*""&ListSheet!R2C3:R99C3&""*"")),""YES"", """")"
Worksheets(currentSheet).Range("L2:L" & lastRow_Cell).FillDown