CODEWITHSUNDEEP
excelvbacountif
Catalin
Catalin

Reputation: 302

COUNTIFS in VBA for multiple columns and rows

I am trying to calculate the number of defects made by each user.

enter image description here

Columns R to X are manually calculate.

I have made a sub to calculate the column X, as it follows:

Sub ci()
Set wb = ThisWorkbook


Set fs = wb.Worksheets("Final")
Set ds = wb.Worksheets("dashboard")

LastRow1 = fs.Range("AN" & fs.Rows.Count).End(xlUp).Row
LastRow2 = fs.Range("AO" & fs.Rows.Count).End(xlUp).Row

Set r1 = fs.Range("AN2:AN" & LastRow1)
Set r2 = fs.Range("AO2:AO" & LastRow2)


x1 = ds.Range("X1")
q2 = ds.Range("Q2")
q3 = ds.Range("Q3")
q4 = ds.Range("Q4")
q5 = ds.Range("Q5")
q6 = ds.Range("Q6")
q7 = ds.Range("Q7")
q8 = ds.Range("Q8")

ds.Range("X2") = Application.WorksheetFunction.CountIfs(r1, x1, r2, q2)
ds.Range("X3") = Application.WorksheetFunction.CountIfs(r1, x1, r2, q3)
ds.Range("X4") = Application.WorksheetFunction.CountIfs(r1, x1, r2, q4)
ds.Range("X5") = Application.WorksheetFunction.CountIfs(r1, x1, r2, q5)
ds.Range("X6") = Application.WorksheetFunction.CountIfs(r1, x1, r2, q6)
ds.Range("X7") = Application.WorksheetFunction.CountIfs(r1, x1, r2, q7)
ds.Range("X8") = Application.WorksheetFunction.CountIfs(r1, x1, r2, q8)
End Sub

The column Q will always have the same values, but the problem is that the number of users will increase or decrease.

How can I integrate a range for the users and simplify my work by populating all the columns(sincerely, I don't want to replicate the code for each column.

Upvotes: 1

Views: 501

Answers (1)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 12008

Not tested but probably you could replace this:

x1 = ds.Range("X1")
q2 = ds.Range("Q2")
q3 = ds.Range("Q3")
q4 = ds.Range("Q4")
q5 = ds.Range("Q5")
q6 = ds.Range("Q6")
q7 = ds.Range("Q7")
q8 = ds.Range("Q8")

ds.Range("X2") = Application.WorksheetFunction.CountIfs(r1, x1, r2, q2)
ds.Range("X3") = Application.WorksheetFunction.CountIfs(r1, x1, r2, q3)
ds.Range("X4") = Application.WorksheetFunction.CountIfs(r1, x1, r2, q4)
ds.Range("X5") = Application.WorksheetFunction.CountIfs(r1, x1, r2, q5)
ds.Range("X6") = Application.WorksheetFunction.CountIfs(r1, x1, r2, q6)
ds.Range("X7") = Application.WorksheetFunction.CountIfs(r1, x1, r2, q7)
ds.Range("X8") = Application.WorksheetFunction.CountIfs(r1, x1, r2, q8)

With this:

Dim i As Integer
Dim ZZ As Integer
ZZ = 18 'column R

Do Until ds.Cells(1, ZZ) = ""
    For i = 2 To 8 Step 1 'i=2 because data starts at row 2
        ds.Cells(i, ZZ) = Application.WorksheetFunction.CountIfs(r1, ds.Cells(1, ZZ), r2, ds.Cells(i, 17)) '17 is column Q
    Next i
    ZZ = ZZ + 1
Loop

It will loop all rows from 2 to 8, for each column, starting at column R, and until it finds and empty cell in row 1 of a column. So it will do R,S,T,... and so on

Upvotes: 1

Related Questions