Search code examples
vbaexcelworksheet-function

how to use countif function having the criteria as a part of string literal?

For example i want to count the number of "johns" appearing in the column A however column has both first name and last name and i want to count all the johns irrespective of their last name.

I am using the below code however doesn't seem to work:

Range("A199").Value = WorksheetFunction.CountIf(Range("A1", Range("A1").End(xlDown)), Cells.Find(What:="John "))
Solution

  • As @YowE3K commented, there is a reason why I use "=" & "John*"). It's a preparation for using a variable in the future instead of the hard-coded "John".

    If you want to use a VBA solution , you can use:

    Range("A199").Value = WorksheetFunction.CountIf(Range("A1", Range("A1").End(xlDown)), "=" & "John*")

    Or, better yet:

    Const NametoFind As String = "John"

Range("A199").Value = WorksheetFunction.CountIf(Range("A1", Range("A1").End(xlDown)), "=" & NametoFind & "*")

    Note: Range("A1", Range("A1").End(xlDown)) will work for continous range only (not if you have blank cells in the middle of you range).