SOLVED

# format formula in macro if(or(countif

Copper Contributor

# format formula in macro if(or(countif

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.

best response confirmed by Slick55 (Copper Contributor)
Solution

# Re: format formula in macro if(or(countif

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
``````
1 best response

Accepted Solutions
best response confirmed by Slick55 (Copper Contributor)
Solution

# Re: format formula in macro if(or(countif

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
``````