How to reduce a repetitive macro (loop maybe?)

Blanchetdb · Sep 28, 2023

Hi

So, I have a macro that is repetitive with the only change is the name being searched. The macro works but I am limited on the number of names because I get an error message saying it is too large. Can someone help on condensing this maybe in a loop (not sure how to do that)

VBA Code:

Private Sub CommandButton1_Click()
Range("F15") = Range("D5")

If Range("D5") <> "" Then
Range("P17:P2000").AutoFilter Field:=1, Criteria1:=Range("D5").Value
End If
 
'All / TOUS
If Range("D5") = "" And Range("AB9") = True Then
Range("I9") = WorksheetFunction.CountIfs(Range("X18:X2000"), "In Progress / Encours", Range("AD18:AD2000"), "0")
Range("I10") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed / Finalisé", Range("AD18:AD2000"), "0")
Range("I11") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Ongoing / Finalisé - continu", Range("AD18:AD2000"), "0")
Range("I12") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Unproductive / Finalisé - improductif", Range("AD18:AD2000"), "0")
Range("I13") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Cancelled / Annulé", Range("AD18:AD2000"), "0")

Range("D9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "01 Planning Phase / Phase de planification", Range("AD18:AD2000"), "0")
Range("D10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "02 Poster Open / Affiche ouverte", Range("AD18:AD2000"), "0")
Range("D11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "03 Screening Phase / Phase de dépistage", Range("AD18:AD2000"), "0")
Range("D12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "04 Assessment Phase / Phase d'évaluation", Range("AD18:AD2000"), "0")
Range("D13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "05 Process Being Finalized / Processus en cours de finalisation", Range("AD18:AD2000"), "0")
Range("G9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "06 Process completed, Pool Created / Processus complété; bassin créé", Range("AD18:AD2000"), "0")
Range("G10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "07 Process completed, No Pool Created / Processus complété; sans bassin créé", Range("AD18:AD2000"), "0")
Range("G11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "08 Cancelled / Annulé", Range("AD18:AD2000"), "0")
Range("G12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "09 Unproductive / Finalisé - improductif", Range("AD18:AD2000"), "0")
Range("G13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "10 Other - See Comments / Autres - Voir commentaires", Range("AD18:AD2000"), "0")
End If
If Range("D5") = "" And Range("AB10") = True Then
Range("I9") = WorksheetFunction.CountIfs(Range("X18:X2000"), "In Progress / Encours", Range("AD18:AD2000"), "1")
Range("I10") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed / Finalisé", Range("AD18:AD2000"), "1")
Range("I11") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Ongoing / Finalisé - continu", Range("AD18:AD2000"), "1")
Range("I12") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Unproductive / Finalisé - improductif", Range("AD18:AD2000"), "1")
Range("I13") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Cancelled / Annulé", Range("AD18:AD2000"), "1")

Range("D9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "01 Planning Phase / Phase de planification", Range("AD18:AD2000"), "1")
Range("D10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "02 Poster Open / Affiche ouverte", Range("AD18:AD2000"), "1")
Range("D11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "03 Screening Phase / Phase de dépistage", Range("AD18:AD2000"), "1")
Range("D12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "04 Assessment Phase / Phase d'évaluation", Range("AD18:AD2000"), "1")
Range("D13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "05 Process Being Finalized / Processus en cours de finalisation", Range("AD18:AD2000"), "1")
Range("G9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "06 Process completed, Pool Created / Processus complété; bassin créé", Range("AD18:AD2000"), "1")
Range("G10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "07 Process completed, No Pool Created / Processus complété; sans bassin créé", Range("AD18:AD2000"), "1")
Range("G11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "08 Cancelled / Annulé", Range("AD18:AD2000"), "1")
Range("G12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "09 Unproductive / Finalisé - improductif", Range("AD18:AD2000"), "1")
Range("G13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "10 Other - See Comments / Autres - Voir commentaires", Range("AD18:AD2000"), "1")
End If
If Range("D5") = "" And Range("AB11") = True Then
Range("I9") = WorksheetFunction.CountIfs(Range("X18:X2000"), "In Progress / Encours", Range("AD18:AD2000"), "2")
Range("I10") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed / Finalisé", Range("AD18:AD2000"), "2")
Range("I11") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Ongoing / Finalisé - continu", Range("AD18:AD2000"), "2")
Range("I12") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Unproductive / Finalisé - improductif", Range("AD18:AD2000"), "2")
Range("I13") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Cancelled / Annulé", Range("AD18:AD2000"), "2")

Range("D9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "01 Planning Phase / Phase de planification", Range("AD18:AD2000"), "2")
Range("D10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "02 Poster Open / Affiche ouverte", Range("AD18:AD2000"), "2")
Range("D11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "03 Screening Phase / Phase de dépistage", Range("AD18:AD2000"), "2")
Range("D12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "04 Assessment Phase / Phase d'évaluation", Range("AD18:AD2000"), "2")
Range("D13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "05 Process Being Finalized / Processus en cours de finalisation", Range("AD18:AD2000"), "2")
Range("G9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "06 Process completed, Pool Created / Processus complété; bassin créé", Range("AD18:AD2000"), "2")
Range("G10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "07 Process completed, No Pool Created / Processus complété; sans bassin créé", Range("AD18:AD2000"), "2")
Range("G11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "08 Cancelled / Annulé", Range("AD18:AD2000"), "2")
Range("G12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "09 Unproductive / Finalisé - improductif", Range("AD18:AD2000"), "2")
Range("G13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "10 Other - See Comments / Autres - Voir commentaires", Range("AD18:AD2000"), "2")
End If
If Range("D5") = "" And Range("AB12") = True Then
Range("I9") = WorksheetFunction.CountIfs(Range("X18:X2000"), "In Progress / Encours", Range("AD18:AD2000"), "3")
Range("I10") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed / Finalisé", Range("AD18:AD2000"), "3")
Range("I11") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Ongoing / Finalisé - continu", Range("AD18:AD2000"), "3")
Range("I12") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Unproductive / Finalisé - improductif", Range("AD18:AD2000"), "3")
Range("I13") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Cancelled / Annulé", Range("AD18:AD2000"), "3")

Range("D9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "01 Planning Phase / Phase de planification", Range("AD18:AD2000"), "3")
Range("D10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "02 Poster Open / Affiche ouverte", Range("P18:P2000"), "3")
Range("D11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "03 Screening Phase / Phase de dépistage", Range("P18:P2000"), "3")
Range("D12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "04 Assessment Phase / Phase d'évaluation", Range("P18:P2000"), "3")
Range("D13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "05 Process Being Finalized / Processus en cours de finalisation", Range("AD18:AD2000"), "3")
Range("G9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "06 Process completed, Pool Created / Processus complété; bassin créé", Range("AD18:AD2000"), "3")
Range("G10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "07 Process completed, No Pool Created / Processus complété; sans bassin créé", Range("AD18:AD2000"), "3")
Range("G11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "08 Cancelled / Annulé", Range("AD18:AD2000"), "3")
Range("G12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "09 Unproductive / Finalisé - improductif", Range("AD18:AD2000"), "3")
Range("G13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "10 Other - See Comments / Autres - Voir commentaires", Range("AD18:AD2000"), "3")
End If

'Jarek Krukowski
If Range("D5") = "Jarek Krukowski" And Range("AB9") = True Then
Range("I9") = WorksheetFunction.CountIfs(Range("X18:X2000"), "In Progress / Encours", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "0")
Range("I10") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed / Finalisé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "0")
Range("I11") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Ongoing / Finalisé - continu", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "0")
Range("I12") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Unproductive / Finalisé - improductif", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "0")
Range("I13") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Cancelled / Annulé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "0")

Range("D9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "01 Planning Phase / Phase de planification", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "0")
Range("D10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "02 Poster Open / Affiche ouverte", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "0")
Range("D11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "03 Screening Phase / Phase de dépistage", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "0")
Range("D12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "04 Assessment Phase / Phase d'évaluation", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "0")
Range("D13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "05 Process Being Finalized / Processus en cours de finalisation", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "0")
Range("G9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "06 Process completed, Pool Created / Processus complété; bassin créé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "0")
Range("G10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "07 Process completed, No Pool Created / Processus complété; sans bassin créé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "0")
Range("G11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "08 Cancelled / Annulé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "0")
Range("G12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "09 Unproductive / Finalisé - improductif", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "0")
Range("G13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "10 Other - See Comments / Autres - Voir commentaires", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "0")
End If
If Range("D5") = "Jarek Krukowski" And Range("AB10") = True Then
Range("I9") = WorksheetFunction.CountIfs(Range("X18:X2000"), "In Progress / Encours", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "1")
Range("I10") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed / Finalisé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "1")
Range("I11") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Ongoing / Finalisé - continu", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "1")
Range("I12") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Unproductive / Finalisé - improductif", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "1")
Range("I13") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Cancelled / Annulé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "1")

Range("D9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "01 Planning Phase / Phase de planification", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "1")
Range("D10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "02 Poster Open / Affiche ouverte", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "1")
Range("D11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "03 Screening Phase / Phase de dépistage", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "1")
Range("D12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "04 Assessment Phase / Phase d'évaluation", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "1")
Range("D13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "05 Process Being Finalized / Processus en cours de finalisation", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "1")
Range("G9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "06 Process completed, Pool Created / Processus complété; bassin créé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "1")
Range("G10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "07 Process completed, No Pool Created / Processus complété; sans bassin créé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "1")
Range("G11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "08 Cancelled / Annulé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "1")
Range("G12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "09 Unproductive / Finalisé - improductif", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "1")
Range("G13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "10 Other - See Comments / Autres - Voir commentaires", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "1")
End If
If Range("D5") = "Jarek Krukowski" And Range("AB11") = True Then
Range("I9") = WorksheetFunction.CountIfs(Range("X18:X2000"), "In Progress / Encours", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "2")
Range("I10") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed / Finalisé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "2")
Range("I11") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Ongoing / Finalisé - continu", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "2")
Range("I12") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Unproductive / Finalisé - improductif", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "2")
Range("I13") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Cancelled / Annulé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "2")

Range("D9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "01 Planning Phase / Phase de planification", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "2")
Range("D10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "02 Poster Open / Affiche ouverte", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "2")
Range("D11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "03 Screening Phase / Phase de dépistage", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "2")
Range("D12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "04 Assessment Phase / Phase d'évaluation", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "2")
Range("D13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "05 Process Being Finalized / Processus en cours de finalisation", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "2")
Range("G9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "06 Process completed, Pool Created / Processus complété; bassin créé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "2")
Range("G10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "07 Process completed, No Pool Created / Processus complété; sans bassin créé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "2")
Range("G11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "08 Cancelled / Annulé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "2")
Range("G12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "09 Unproductive / Finalisé - improductif", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "2")
Range("G13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "10 Other - See Comments / Autres - Voir commentaires", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "2")
End If
If Range("D5") = "Jarek Krukowski" And Range("AB12") = True Then
Range("I9") = WorksheetFunction.CountIfs(Range("X18:X2000"), "In Progress / Encours", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "3")
Range("I10") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed / Finalisé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "3")
Range("I11") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Ongoing / Finalisé - continu", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "3")
Range("I12") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Unproductive / Finalisé - improductif", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "3")
Range("I13") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Cancelled / Annulé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "3")

Range("D9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "01 Planning Phase / Phase de planification", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "3")
Range("D10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "02 Poster Open / Affiche ouverte", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "3")
Range("D11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "03 Screening Phase / Phase de dépistage", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "3")
Range("D12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "04 Assessment Phase / Phase d'évaluation", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "3")
Range("D13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "05 Process Being Finalized / Processus en cours de finalisation", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "3")
Range("G9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "06 Process completed, Pool Created / Processus complété; bassin créé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "3")
Range("G10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "07 Process completed, No Pool Created / Processus complété; sans bassin créé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "3")
Range("G11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "08 Cancelled / Annulé", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "3")
Range("G12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "09 Unproductive / Finalisé - improductif", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "3")
Range("G13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "10 Other - See Comments / Autres - Voir commentaires", Range("P18:P2000"), "Jarek Krukowski", Range("AD18:AD2000"), "3")
End If


'Genève Fournier
If Range("D5") = "Genève Fournier" And Range("AB9") = True Then
Range("I9") = WorksheetFunction.CountIfs(Range("X18:X2000"), "In Progress / Encours", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "0")
Range("I10") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed / Finalisé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "0")
Range("I11") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Ongoing / Finalisé - continu", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "0")
Range("I12") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Unproductive / Finalisé - improductif", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "0")
Range("I13") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Cancelled / Annulé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "0")

Range("D9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "01 Planning Phase / Phase de planification", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "0")
Range("D10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "02 Poster Open / Affiche ouverte", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "0")
Range("D11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "03 Screening Phase / Phase de dépistage", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "0")
Range("D12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "04 Assessment Phase / Phase d'évaluation", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "0")
Range("D13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "05 Process Being Finalized / Processus en cours de finalisation", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "0")
Range("G9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "06 Process completed, Pool Created / Processus complété; bassin créé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "0")
Range("G10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "07 Process completed, No Pool Created / Processus complété; sans bassin créé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "0")
Range("G11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "08 Cancelled / Annulé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "0")
Range("G12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "09 Unproductive / Finalisé - improductif", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "0")
Range("G13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "10 Other - See Comments / Autres - Voir commentaires", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "0")
End If
If Range("D5") = "Genève Fournier" And Range("AB10") = True Then
Range("I9") = WorksheetFunction.CountIfs(Range("X18:X2000"), "In Progress / Encours", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "1")
Range("I10") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed / Finalisé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "1")
Range("I11") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Ongoing / Finalisé - continu", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "1")
Range("I12") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Unproductive / Finalisé - improductif", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "1")
Range("I13") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Cancelled / Annulé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "1")

Range("D9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "01 Planning Phase / Phase de planification", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "1")
Range("D10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "02 Poster Open / Affiche ouverte", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "1")
Range("D11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "03 Screening Phase / Phase de dépistage", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "1")
Range("D12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "04 Assessment Phase / Phase d'évaluation", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "1")
Range("D13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "05 Process Being Finalized / Processus en cours de finalisation", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "1")
Range("G9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "06 Process completed, Pool Created / Processus complété; bassin créé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "1")
Range("G10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "07 Process completed, No Pool Created / Processus complété; sans bassin créé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "1")
Range("G11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "08 Cancelled / Annulé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "1")
Range("G12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "09 Unproductive / Finalisé - improductif", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "1")
Range("G13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "10 Other - See Comments / Autres - Voir commentaires", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "1")
End If
If Range("D5") = "Genève Fournier" And Range("AB11") = True Then
Range("I9") = WorksheetFunction.CountIfs(Range("X18:X2000"), "In Progress / Encours", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "2")
Range("I10") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed / Finalisé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "2")
Range("I11") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Ongoing / Finalisé - continu", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "2")
Range("I12") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Unproductive / Finalisé - improductif", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "2")
Range("I13") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Cancelled / Annulé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "2")

Range("D9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "01 Planning Phase / Phase de planification", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "2")
Range("D10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "02 Poster Open / Affiche ouverte", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "2")
Range("D11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "03 Screening Phase / Phase de dépistage", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "2")
Range("D12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "04 Assessment Phase / Phase d'évaluation", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "2")
Range("D13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "05 Process Being Finalized / Processus en cours de finalisation", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "2")
Range("G9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "06 Process completed, Pool Created / Processus complété; bassin créé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "2")
Range("G10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "07 Process completed, No Pool Created / Processus complété; sans bassin créé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "2")
Range("G11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "08 Cancelled / Annulé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "2")
Range("G12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "09 Unproductive / Finalisé - improductif", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "2")
Range("G13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "10 Other - See Comments / Autres - Voir commentaires", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "2")
End If
If Range("D5") = "Genève Fournier" And Range("AB12") = True Then
Range("I9") = WorksheetFunction.CountIfs(Range("X18:X2000"), "In Progress / Encours", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "3")
Range("I10") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed / Finalisé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "3")
Range("I11") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Ongoing / Finalisé - continu", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "3")
Range("I12") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Completed - Unproductive / Finalisé - improductif", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "3")
Range("I13") = WorksheetFunction.CountIfs(Range("X18:X2000"), "Cancelled / Annulé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "3")

Range("D9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "01 Planning Phase / Phase de planification", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "3")
Range("D10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "02 Poster Open / Affiche ouverte", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "3")
Range("D11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "03 Screening Phase / Phase de dépistage", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "3")
Range("D12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "04 Assessment Phase / Phase d'évaluation", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "3")
Range("D13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "05 Process Being Finalized / Processus en cours de finalisation", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "3")
Range("G9") = WorksheetFunction.CountIfs(Range("T18:T2000"), "06 Process completed, Pool Created / Processus complété; bassin créé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "3")
Range("G10") = WorksheetFunction.CountIfs(Range("T18:T2000"), "07 Process completed, No Pool Created / Processus complété; sans bassin créé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "3")
Range("G11") = WorksheetFunction.CountIfs(Range("T18:T2000"), "08 Cancelled / Annulé", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "3")
Range("G12") = WorksheetFunction.CountIfs(Range("T18:T2000"), "09 Unproductive / Finalisé - improductif", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "3")
Range("G13") = WorksheetFunction.CountIfs(Range("T18:T2000"), "10 Other - See Comments / Autres - Voir commentaires", Range("P18:P2000"), "Genève Fournier", Range("AD18:AD2000"), "3")
End If

this macro repeats for another 3 names but then I am unable to add additional names

can someone please provide a solution on how to reduce this?

thanks

MARK858 · Oct 1, 2023

Blanchetdb said:
and I tried what you recently sent me, and it gives me the same error message.

If you click Tools - References do any of them have the word MISSING

duggie33 · Oct 1, 2023

Is "Results" declared as a variant for the array?

MARK858 · Oct 1, 2023

duggie33 said:
Is "Results" declared as a variant for the array?

It doesn't need declaring as a Variant. The code @kevin9999 posted runs as is (see the workbook kevin9999 attached in the link in post 18).
The use of ReDim is enough to stop the Option Explicit objecting to the variable.

duggie33 · Oct 1, 2023

I thought Option Explicit required everything to be declared.

MARK858 · Oct 1, 2023

The Option Explicit treats ReDim as declaring the variable even though it is only re-dimensioning it.
You are ok using the Redim as long as the variable doen't appear before the Redim...

VBA Code:

'Invalid

Option Explicit

Sub test()
    arr(1, 1) = 0
    ReDim arr(1 To 10, 1 To 2)
End Sub

VBA Code:

'Valid

Option Explicit

Sub test()
    ReDim arr(1 To 10, 1 To 2)
    arr(1, 1) = 0
End Sub

duggie33 · Oct 1, 2023

Thanks Mark!

kevin9999 · Oct 1, 2023

Blanchetdb said:
and I tried what you recently sent me, and it gives me the same error message.

Thank you for making your actual file available. I added the code from post #18 and it ran without a hitch. (Used Renee Godse as the test name.)
I've attached a link to the file with the result in place. If it still doesn't work for you, then it's probably as @MARK858 suggested and you're missing a reference on your version of Excel/VBA.
To be honest, I think you're probably overcomplicating things by using code when - as @duggie33 suggested - a fairly straightforward worksheet formula would do the same thing. I've also attached a link to your file with formulas in place - also with Renee Godse as a test name - and you'll see what I mean. If that option works for you, all credit to @duggie33 for his suggestion.
Test Site.xlsm
Test Site Duggie33.xlsm

duggie33 · Oct 2, 2023

Here is a mini sheet with the formulas that seem to work on the small set of data. I manually checked a few of the results. The results shown are for the HR Advisor option being blank.

Test Site Duggie33.xlsm

B

C

D

E

F

G

H

I

9

01 Planning Phase / Phase de planification

0

06 Process completed, Pool Created / Processus complété; bassin créé

19

In Progress / Encours

1

10

02 Poster Open / Affiche ouverte

1

07 Process completed, No Pool / Processus complété; sans bassin

0

Completed / Finalisé

4

11

03 Screening Phase / Phase de dépistage

0

08 Cancelled / Annulé

4

Completed - Ongoing / Finalisé - continu

16

12

04 Assessment Phase / Phase d'évaluation

0

09 Unproductive / Finalisé - improductif

2

Completed - Unproductive / Finalisé - improductif

2

13

05 Process Being Finalized / Processus en cours de finalisation

0

10 Other - See Comments / Autres - Voir commentaires

0

Cancelled / Annulé

4

HRAdvisor

Cell Formulas
Range	Formula
D9:D13	D9	=COUNTIFS($T$18:$T$2000,B9,$AD$18:$AD$2000,$AB$13,$P$18:$P$2000,IF($D$5="","*",$D$5))
G9:G13	G9	=COUNTIFS($T$18:$T$2000,E9,$P$18:$P$2000,IF($D$5="","*",$D$5),$AD$18:$AD$2000,$AB$13)
I9:I13	I9	=COUNTIFS($X$18:$X$2000,H9,$P$18:$P$2000,IF($D$5="","*",$D$5),$AD$18:$AD$2000,$AB$13)

Doug

How to reduce a repetitive macro (loop maybe?)

Blanchetdb

Board Regular

MARK858

MrExcel MVP

Excel Facts

duggie33

Active Member

MARK858

MrExcel MVP

duggie33

Active Member

MARK858

MrExcel MVP

duggie33

Active Member

kevin9999

Well-known Member

duggie33

Active Member

Forum statistics

Share this page

How to reduce a repetitive macro (loop maybe?)

Board Regular

MrExcel MVP

Excel Facts

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Active Member

Well-known Member

Active Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock