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 t...
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Dec 18, 2021

    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
HansVogelaar's avatar
HansVogelaar
MVP
Dec 18, 2021

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