CODEWITHSUNDEEP
excelvba
CJRobinson
CJRobinson

Reputation: 11

VBA for CountIFS and loop

I am trying to create a column on a separate worksheet from a table of values using a countifs function. The following code works great for one cell and static criteria but I want it to repeat down column B on "FilteredData" sheet using cell "B1" (Pay period #) and column A:A values as the other criteria (Employee Names). I am assuming that I need to imbed a loop into the countifs function but am at a loss and could use some guidance. 

Sub countIfs()

Dim wsSourceData As Worksheet
Set wsSourceData = Worksheets("Data")

Sheets("FilteredData").Range("B2").Value2 = _
    Excel.WorksheetFunction.countIfs( _
    wsSourceData.[B:B], "Jane Doe", wsSourceData.[F:F], "Pay Period #")

End Sub

Upvotes: 1

Views: 733

Answers (1)

Zsmaster
Zsmaster

Reputation: 1559

You can use parameters

For example:

Sub countIfs(Line As Long, fCrit As String, sCrit As String)
    Dim wsSourceData As Worksheet
    Set wsSourceData = Worksheets("Data")

    Sheets("FilteredData").Range("A" & Line).Value2 = fCrit & " " & sCrit
    Sheets("FilteredData").Range("B" & Line).Value2 = Excel.WorksheetFunction.countIfs(wsSourceData.[B:B], fCrit, wsSourceData.[F:F], sCrit)
End Sub

Public Sub Prog()
    Dim fCrit(1 To 2) As String
    Dim sCrit(1 To 2) As String

    fCrit(1) = "Jane Doe"
    fCrit(2) = "John Doe"

    sCrit(1) = "Pay Period #"
    sCrit(2) = "Pay Period2 #"

    For i = 1 To UBound(fCrit)
        Call countIfs(i + 1, fCrit(i), sCrit(i))
    Next i
End Sub

Upvotes: 2

Related Questions