CODEWITHSUNDEEP
vbaexcelexcel-2013worksheet-function
IcyPopTarts
IcyPopTarts

Reputation: 504

CountIf() Function From VBA

I am attempting to use the CountIf() function from my VBA syntax, but I get an error of

Wrong number of arguments or invalid property assignment

This is my syntax - what do I need to change so this is successful?

Dim countofemps As Integer

Sheets("All emps").Select
Selection.AutoFilter
ActiveSheet.ListObjects("Allemps").Range.AutoFilter Field:=1, Criteria1:=filtervalue
ActiveSheet.ListObjects("Allemps").Range.AutoFilter Field:=5, _
    Criteria1:=">=1/29/2017", Operator:=xlAnd, Criteria2:="<=3/1/2017"
'This line throws error
countofemps = WorksheetFunction.CountIf(Range("'All emps'!A2:A2000"), filtervalue, Range("'All emps'!B2:B2000"))

Upvotes: 1

Views: 3031

Answers (2)

A.S.H
A.S.H

Reputation: 29332

I guess what you want is CountIfs, with the criteria for the second argument column is "not empty":

Since you also want to filter the dates on column 5, you can integrate all your conditions in the countifs function (without the need for filtering), like this:

With Sheets("All emps").ListObjects("Allemps").DataBodyRange
    countofemps = WorksheetFunction.CountIfs( _
        .Columns(5),  ">=1/29/2017", _
        .Columns(5), "<=3/1/2017", _
        .Columns(1), filtervalue, _
        .Columns(3), "<>")
End With

Upvotes: 0

user3598756
user3598756

Reputation: 29421

I'd go as follows:

With Sheets("All emps") '<--| reference your sheet
    With .ListObjects("Allemps").Range '<--| reference its "Allemps" table range
        .AutoFilter '<--| clear any previous filter
        .AutoFilter Field:=1, Criteria1:=filtervalue '<--| filter referenced range on its first column with given filtervalue
        .AutoFilter Field:=5, Criteria1:=">=1/29/2017", Operator:=xlAnd, Criteria2:="<=3/1/2017" '<--| filter reference range on its fifth column with given limit dates
        countofemps = Application.WorksheetFunction.Subtotal(103, .Columns(1)) -1  '<--| count the number of filtered cells on its first column except header cellr
    End With
End With

Upvotes: 1

Related Questions