put excel worksheet function in a cell via VBA

[Nahkranoth]

Hello,

I hope you can help with this issue i've been trying to solve in the past day

The thing is after i finish my extraction of info from a txt file to excel, i format and populate the top cells will statistical info

= "bsc1: " & COUNTIF($A$3:$A$3001;"bsc1") -> this is the first one that i did manually beforehand in excel

I've tried to put the same thing but via VBA with the following:

BSCName = "= """"" & BSCName & ":"""" & COUNTIF($A$3:$A$3001,""""" & BSCName & """"")"
newS.Cells(1, cnt).Formula = BSCName

The result of the BSCName variable would be this string formatted for excel via VBA.
"= ""bsc1:"" & COUNTIF($A$3:$A$3001,""bsc1"")"

It would be good to work but it gives me an error 1004: Application-defined or object-defined error"

Could you help me and point out on what's wrong with this?