Option Compare Text 'la casse est ignorée
Private Sub Worksheet_Activate()
Dim t, rest(), ncol%, i&, cpt%, j%, n&
With Feuil2 'CodeName de la feuille des congés
t = .Range("A1", .UsedRange) 'matrice, plus rapide
End With
ReDim rest(1 To UBound(t) - 4, 1 To 3)
ncol = UBound(t, 2)
'---remplissage du tableau---
For i = 5 To UBound(t)
cpt = 0
For j = 22 To ncol
If t(i, j) = "MALADIE" Then cpt = cpt + 1
Next
If cpt Then
n = n + 1
rest(n, 1) = t(i, 2)
rest(n, 2) = t(i, 3)
rest(n, 3) = cpt
End If
Next
'---restitution---
If n Then
Application.ScreenUpdating = False
[A3].Resize(n, 3) = rest
Rows(n + 3 & ":" & Rows.Count).Delete
[A3].Resize(n, 3).Sort [C3], xlDescending, Header:=xlNo 'tri décroissant
End If
End Sub