Sub essai()
Dim i As Long
Dim j As Long
Dim nb As Integer
Dim d(100000, 5)
Dim a
a = Feuil1.[A1].CurrentRegion.Value ' dans un tableau en mémoire
For i = 2 To UBound(a, 1)
If a(i, 1) = "nom3" Or a(i, 1) = "nom4" Then
d(i, 1) = a(i, 1) 'nom
d(i, 2) = a(i, 6) 'date
d(i, 3) = a(i, 7) 'demi
d(i, 4) = a(i, 8) 'activité
End If
Next
For i = 5 To 11
nb = 0
For j = 2 To Feuil1.Range("A" & Rows.Count).End(xlUp).Row
If (d(j, 2) = Feuil3.Cells(1, i) And (d(j, 1) = "nom3" Or d(j, 1) = "nom4")) Then
If (d(j, 4) = "Activité15" Or d(j, 4) = "Activité16") Then nb = nb + 1
End If
Next
If nb = 0 Then Feuil3.Cells(2, i) = "" Else Feuil3.Cells(2, i) = nb / 2
Next
End Sub