countif in macros gives 0 results

Question

​I was trying to do a countif in a column B named First Name that has different names in it but the results is returning 0.

Here is my code:

Public Sub counting() Dim lastcell As String

Range("B2").Select

Selection.End(xlDown).Select
lastcell = ActiveCell.Address

ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "=countif(B2:" + lastcell + ", John)"

End Sub

If I check the formula written in the active cell it is:

=COUNTIF(B2:$B$16, John)

Please help.

I tried changing the line from

ActiveCell.Value = "=countif(B2:" + lastcell + ", John)"

to

ActiveCell.Value = "=countif(B2:" + lastcell + ", "John")" still not working.

Answer 1

Try,

ActiveCell.formula = "=countif(B2:" & lastcell & chr(44) & chr(34) &"John" &chr(34) & ")"

Answer 2

Public Sub counting()
    With Range("B2").End(xlDown)
        .Offset(1, 0).Formula = "=COUNTIF(B2:" & .Address(False, False) & ", ""John"")"
    End with
End Sub