Sub Compter()
Dim tablo() As Variant
Set dico = CreateObject("Scripting.dictionary")
With Sheets("Feuil2")
Fin = .UsedRange.Rows.Count
tablo = .Range("A2:D" & Fin).Value
End With
For i = LBound(tablo, 1) To UBound(tablo, 1)
If Not dico.exists(tablo(i, 2) & "-" & tablo(i, 1)) Then
dico.Add tablo(i, 2) & "-" & tablo(i, 1), tablo(i, 4)
End If
Next i
'Recherche 1==>Eau - 2013
TypeSol = "eau"
annee = 2013
Rech = 0
For Each ele In dico.keys
If CInt(Split(ele, "-")(1)) = annee And UCase(dico.Item(ele)) = UCase(TypeSol) Then
Rech = Rech + 1
End If
Next ele
Range("G3") = Rech
'Recherche 2==>Eau - 2014
TypeSol = "eau"
annee = 2014
Rech = 0
For Each ele In dico.keys
If CInt(Split(ele, "-")(1)) = annee And UCase(dico.Item(ele)) = UCase(TypeSol) Then
Rech = Rech + 1
End If
Next ele
Range("H3") = Rech
'Recherche 3==>Sable - 2013
TypeSol = "Sable"
annee = 2013
Rech = 0
For Each ele In dico.keys
If CInt(Split(ele, "-")(1)) = annee And UCase(dico.Item(ele)) = UCase(TypeSol) Then
Rech = Rech + 1
End If
Next ele
Range("G4") = Rech
'Recherche 4==>Sable - 2014
TypeSol = "Sable"
annee = 2014
Rech = 0
For Each ele In dico.keys
If CInt(Split(ele, "-")(1)) = annee And UCase(dico.Item(ele)) = UCase(TypeSol) Then
Rech = Rech + 1
End If
Next ele
Range("H4") = Rech
'Recherche 3==>Terre- 2013
TypeSol = "Terre"
annee = 2013
Rech = 0
For Each ele In dico.keys
If CInt(Split(ele, "-")(1)) = annee And UCase(dico.Item(ele)) = UCase(TypeSol) Then
Rech = Rech + 1
End If
Next ele
Range("G5") = Rech
'Recherche 4==>Terre- 2014
TypeSol = "Terre"
annee = 2014
Rech = 0
For Each ele In dico.keys
If CInt(Split(ele, "-")(1)) = annee And UCase(dico.Item(ele)) = UCase(TypeSol) Then
Rech = Rech + 1
End If
Next ele
Range("H5") = Rech
End Sub