Sub retrouver_valeurs()
Dim Ltest As Integer, Lref As Integer, i As Integer, j As Long, c As Long, ii As Long
Dim Tbtest(), Tbref(), TbR, TbT, Tbtest2()
Dim DicTest As Object, DicRef As Object, d As Object
Dim tblDate, a, b(), cle
tblDate = Feuil4.[K2:L9]
With Sheets("BD_Ref")
Lref = .Range("A" & Rows.Count).End(xlUp).Row
Tbref = .Range("c2:d" & Lref).Value
Set DicRef = CreateObject("Scripting.Dictionary")
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(Tbref)
If Tbref(i, 2) <> "M1" Then 'on ne prend pas la famille M1
DicRef(Tbref(i, 1) & Tbref(i, 2)) = ""
End If
Next i
End With
With Sheets("BD_Test")
Ltest = .Range("A" & Rows.Count).End(xlUp).Row
Tbtest = .Range("A2:R" & Ltest).Value
Set DicTest = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(tblDate)
' c = 0
For j = 1 To UBound(Tbtest)
d(Tbtest(j, 3)) = "" 'd(Tbtest(j, 4)) = "" '///colonne date
If Tbtest(j, 18) = "M" Or Tbtest(j, 18) = "M/A" Then 'on ne prend que pour Test= M ou M/A
If Tbtest(j, 4) <> "M1" Then '////pour exclure M1 du traitement
If Year(Tbtest(j, 3)) <= tblDate(i, 2) Then '/////on extrait année de Tbtest pour comparer
'cle=famille&sous-famille -----> item= date test
' If Not DicTest.Exists(Tbtest(j, 4) & Tbtest(j, 5)) Then DicTest.Add Tbtest(j, 4) & Tbtest(j, 5), Tbtest(j, 3)
DicTest(Tbtest(j, 4) & " - " & Tbtest(j, 5) & " - " & Tbtest(j, 3)) = ""
End If
End If
End If
Next j
Next i
For Each cle In d.keys
c = 0
For j = 1 To UBound(tblDate)
If tblDate(j, 1) = cle Then c = c + 1: Exit For
Next j
If c = 0 Then
ii = ii + 1
ReDim Preserve b(1 To 1, 1 To ii): b(1, ii) = cle
End If
Next cle
End With
a = DicTest.keys
b = Application.Transpose(b)
Feuil5.[A38].Resize(DicTest.Count) = Application.Transpose(a)
If d.Count > 0 Then
Feuil5.[C38].Resize(UBound(b, 1), UBound(b, 2)) = b
End If
End Sub