Forum Discussion

Slick55's avatar
Slick55
Copper Contributor
Dec 18, 2021
Solved

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.

  • 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
    

1 Reply

  • 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
    

Resources