excel - VBA Set Cell value to text containing ' not working -

I try to get a macro to type a pile of formulas for my formula something like this Go to:

  = COUNTIF ('other_sheet'! A: A, "hello *")  

User A and other_sheets, so I select the cell And then

  ActiveCell.Value = "= COUNTIF ('' & amp; Other Cellulus & amp; & # 39;; & # 39; & # 39; Amp; Columns & amp;": "& amp; Columns And "" ", hello *" ")"  

but it is giving me errors like:

1004: host Defined error

I have tried to use ActiveCell.Text , ActiveCell.Formula etc. and they all do not work

It should be ActiveCell.Formula and ", The wrong way is next to Hello . It should be:

  ActiveCell.Formula = "= COUNTIF ('' and other cellulus & '!! & Amp; Amp; column_ & amp;;": "& Amp; Columns" Amp; "," "hello *" ")"