Find number of occurrences of a partial string in a single column using multiple conditions

Question

I've a sheet "Employee_log" which contains the details of everyday log in a single string in column A. For example:

A1 : EMP1~EMP2~JOB1~JOB2...
A2 : EMP1~EMP3~JOB1~JOB3...
A3 : EMP2~EMP3~JOB2~JOB3...

Now I want the number of occurrences of EMP1 and JOB1 from this column and populate into another sheet "Ind_Detail":

I'm using, but it's not working properly,:

Set DSht = Worksheets("Employee_log")

Dsht. Activate  

ThisWorkbook.Sheets("Ind_Detail").Cells(1, "A") =CountIfs(Range("A:A"), _
                                              "EMP1", Range("A:A"), "JOB1")

Set DSht = Nothing

score 0 · Answer 1 · edited May 23 '17 at 12:14

0

You need to wrap the criteria in wildcard characters to count if a 'string in a string' occurs.

with Worksheets("Employee_log")
    ThisWorkbook.Sheets("Ind_Detail").Cells(1, "A") = _
      application.CountIfs(.columns(1), "*EMP1*", .columns(1), "*JOB1*")
end with

I've also blocked the worksheet into a With ... End With statement so that the ranges (e.g. .Columns(1) in the sample) could be localized to the Employee_log worksheet. Your sample of Range("A:A") left the definition of the parent worksheet ambiguous.

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

edited May 23 '17 at 12:14

Community

1
1

answered Sep 17 '15 at 00:56

is Countifs a member of the application object? didnt know that, thought we have to use WorksheetFunction – A.S.H Sep 17 '15 at 01:06
1

Pretty much any worksheet function can be called with just Application. I very rarely use the [WorksheetFunction object](https://msdn.microsoft.com/en-us/library/office/ff834434.aspx) but I use worksheet functions all the time. – Sep 17 '15 at 01:17
yeah, it does not show up with intellisense though. Thx for the info – A.S.H Sep 17 '15 at 01:40
@SouravSarkar - [Glad you got sorted out](http://stackoverflow.com/help/someone-answers). – Sep 17 '15 at 20:17

score 0 · Answer 2 · answered Sep 17 '15 at 00:57

CountIfs is a WorkSheet function, it cannot be used directly in VBA. Either write your formula directly in the destination cell:

=CountIfs(Employee_log!A:A, "*EMP1*", Employee_log!A:A, "*JOB1*")

or, in VBA, use the WorksheetFunction object to access Worksheet functions:

ThisWorkbook.Sheets("Ind_Detail").Cells(1, "A") =WorkSheetFunction.CountIfs(Range("A:A"), "*EMP1*", Range("A:A"), "*JOB1*")

EDIT: added wildcards as in Jeeped's answer

Find number of occurrences of a partial string in a single column using multiple conditions

2 Answers2