Sub PV_Manquant()
Dim Ltest As Integer, L As Long, i As Integer, j As Long, c As Long, ii As Long
Dim Tbtest, Tbref, tbManquant(), TbT(), Tbtest2()
Dim DicTest As Object, D As New Dictionary, DicRef As New Dictionary
Dim tblDate(1 To 8, 1 To 2), a, b(), cle, Dt
Dim BdRef As Worksheet, BdTest As Worksheet, item, item1, test As Boolean, x
Set BdRef = ThisWorkbook.Worksheets("BD_Ref")
Set BdTest = ThisWorkbook.Worksheets("BD_Test")
Set DicRef = CreateObject("Scripting.Dictionary")
Set DicTest = CreateObject("Scripting.Dictionary")
'Tableau date d'acquisition
tblDate(1, 1) = "T1": tblDate(1, 2) = 1960
tblDate(2, 1) = "H2": tblDate(2, 2) = 1960
tblDate(3, 1) = "O1": tblDate(3, 2) = 1982
tblDate(4, 1) = "G1": tblDate(4, 2) = 1986
tblDate(5, 1) = "L1": tblDate(5, 2) = 1996
tblDate(6, 1) = "G2": tblDate(6, 2) = 2000
tblDate(7, 1) = "DL1": tblDate(7, 2) = 2004
tblDate(8, 1) = "RO1": tblDate(8, 2) = 2006
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Feuil3.Cells.Clear '***pour tester à retirer du code
'
' Feuil3.[J2].Resize(UBound(tblDate, 1), UBound(tblDate, 2)).Value = tblDate '***pour tester
L = BdRef.Range("A" & Rows.Count).End(xlUp).Row
Tbref = BdRef.Range("c2:d" & L).Value
'famille M1 exclue
For L = 1 To UBound(Tbref)
If Tbref(L, 1) <> "M1" Then D(Tbref(L, 1) & "-" & Tbref(L, 2)) = Tbref(L, 1) & "-" & Tbref(L, 2)
Next L
'i = 0
'For Each Item In Dfamille.Count
'i = i + 1
'ReDim Preserve Tbref(1 To 3, 1 To i): Tbref(1, 1) = Item
'
'Next Item
L = BdTest.Range("A" & Rows.Count).End(xlUp).Row
Tbtest = BdTest.Range("A2:R" & L).Value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For i = 1 To UBound(Tbtest) 'M et M/A inclu,M1 exclu
If Tbtest(i, 4) <> "M1" Then
If Tbtest(i, 18) = "M" Or Tbtest(i, 18) = "M/A" Then 'on ne prend que pour Test= M ou M/A
If Tbtest(i, 5) <> "" Then DicTest(Tbtest(i, 4) & "-" & Tbtest(i, 5) & "-" & Tbtest(i, 3)) = Tbtest(i, 4) & "-" & Tbtest(i, 5) & "-" & Tbtest(i, 3)
End If
End If
Next i
i = 0: j = 0
'compare ref et test
For Each item In D.items
test = True
For Each item1 In DicTest.items
If UCase(Left(item1, Len(item))) = UCase(item) Then
i = i + 1
ReDim Preserve TbT(1 To i): TbT(i) = item1
test = False: Exit For
End If
Next item1
If test Then
j = j + 1
ReDim Preserve tbManquant(1 To j): tbManquant(j) = item
'MsgBox item 'manquant dans tbref
End If
Next item
For i = 1 To UBound(TbT)
Feuil6.Range("P" & i + 1) = TbT(i)
Next i
For i = 1 To UBound(tbManquant)
Feuil6.Range("R" & i + 1) = tbManquant(i)
Next i
' For j = 1 To UBound(Tbref)
' If Tbref(j, 1) <> "M1" Then 'on ne prend pas la famille M1
' ' MsgBox Tbref(j, 2)
' DicRef(Tbref(j, 1) & "|" & Tbref(j, 2)) = ""
' End If
' Next j
'
' Feuil3.[P2].Resize(DicRef.Count) = Application.Transpose(DicRef.keys)
' Feuil3.[R2].Resize(D.Count) = Application.Transpose(D.keys)
'
' MsgBox D.Count
' Stop
' For i = 1 To UBound(tblDate)
' 'MsgBox tblDate(i, 1) & "|" & tblDate(i, 2)
' ' 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)
' ' d(Tbtest(j, 3)) = ""
' DicTest(Tbtest(j, 4) & "|" & Tbtest(j, 5) & "|" & Tbtest(j, 3)) = ""
' 'If Not d.exists(Tbtest(j, 3)) Then d.Add Tbtest(j, 3), Tbtest(j, 3)
'
' End If
' End If
' End If
' Next j
' Next i
' Feuil3.[H2].Resize(DicTest.Count) = Application.Transpose(DicTest.keys)
End Sub