CountIF from activeCell vba

[Alroj]

Hi Team,

I have this formula in my macro that I was hoping it will work but so far it is giving me an error time 1004.
Would someone be able to assist and let me know if I have missed something please
Much appreciated

Range("s1").Select
    
ActiveCell.FormulaR1C1 = "=COUNTIF(" & ActiveCell.offset(0,1).Address(RowAbsolute:=False) & ":" & ActiveCell.Offset(2, 10).Address(RowAbsolute:=False) & ",""*Day*"")"

 

[Alex Blakenburg]

Just change it to:
ActiveCell.Formula

 

[Alroj]

..... and with the change you suggested, the formula works! Thank you very much for your valuable insight, Alex Blakenburg!
much appreciated!

 

[Alex Blakenburg]

You're welcome. Glad I could help.

 

[Peter_SSs]

You could also have done it with the R1C1 notation like this

With ActiveCell
  .FormulaR1C1 = "=countif(RC" & .Column + 1 & ":R[2]C" & .Column + 10 & ",""*Day*"")"
End With