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.

that is not `countif in a macro`. you are using a macro to place a formula into a cell (same as if you have typed it in). check the resulting formula in the worksheet cell to see if it is correct and make corrections to your code if it is not correct ..... note: do not use `select ... activecell` ... use `ActiveCell.Offset(1, 0).Value = "=countif(B2:" + lastcell + ", John)"` — jsotola, Oct 19 '17 at 00:07
use `ActiveCell.Offset(1, 0).Value = "=countif(B2:" + lastcell + ", ""John"")"` — jsotola, Oct 19 '17 at 04:34
Will I able to loop on that too? For example, it will count the number of "John", "Mark", "Mike" and highlight the row of that name (in any color) if it's more than 50% of the total count. Let's say there are 10 entries and there are 5 or more "John" in that column. Thank you so much for the help. Really appreciate it. — Jonathan, Oct 19 '17 at 09:11

score 1 · Answer 1 · answered Oct 19 '17 at 00:14

1

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

answered Oct 19 '17 at 00:14

Tim Williams

154,628
8
97
125

Thank you for answering my query. I tried your code (copy+paste) and it is giving me a syntax error. – Jonathan Oct 19 '17 at 03:22
that should have worked ... are any of the lines colored red in the editor? ... do you have any data in column B ? – jsotola Oct 19 '17 at 04:30
Works for me. It will fail if there's no content in B3 though. – Tim Williams Oct 19 '17 at 05:08
Hi Tim, oh sorry need to inform you that it's in a table style medium 2( blue and white. Thank you Tim. – Jonathan Oct 19 '17 at 06:56
makes no difference what color the cells are – jsotola Oct 19 '17 at 07:38

score 0 · Answer 2 · answered Oct 19 '17 at 00:03

0

Try,

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

answered Oct 19 '17 at 00:03

Hi Jeeped, Thank you for answering my query. It is returning a 0 as a results. – Jonathan Oct 19 '17 at 03:28

countif in macros gives 0 results

2 Answers2