1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
| Sub testtttt()
Application.ScreenUpdating = False
Dim f1 As Worksheet
Dim f2 As Worksheet
Dim f3 As Worksheet
Set f1 = Sheets("Test1")
Set f2 = Sheets("Test2")
Set f3 = Sheets("RESULTAT")
Dim TblBD1
Dim TblBD2
Dim i As Integer
Dim j As Integer
Dim Lig As Long
Dim Identique As Boolean
Dim Test1NonTest2 As Boolean
Dim Test2NonTest1 As Boolean
f3.Cells.ClearContents
f3.Cells(1, 1) = "IDENTIQUE"
f3.Cells(1, 2) = "existe en test 1 et non en test 2"
f3.Cells(1, 3) = "existe en test 2 et non en test 1"
TblBD1 = f1.Range("A2:A" & f1.Range("A" & Rows.Count).End(xlUp).Row)
TblBD2 = f2.Range("A2:A" & f2.Range("A" & Rows.Count).End(xlUp).Row)
Lig = 2
For i = LBound(TblBD1) To UBound(TblBD1)
Identique = False
Test1NonTest2 = False
Test2NonTest1 = False
'*************************************Article identique *********************************
For j = LBound(TblBD2) To UBound(TblBD2)
If TblBD2(j, 1) = TblBD1(i, 1) Then
Identique = True
Exit For
End If
Next j
If Identique = True Then f3.Cells(Lig, 1) = TblBD2(j, 1)
If f3.Cells(Lig, 1) <> "" Then Lig = Lig + 1
Next i
Lig = 2
'*************************************Figure en test1 et non en Test2 *********************************
Dim plage1 As Range
Dim plage2 As Range
Dim Cel As Range
Dim dernligne As Long
Set plage1 = f1.Range("A2:A" & f1.Range("A" & Rows.Count).End(xlUp).Row)
Set plage2 = f2.Range("A2:A" & f2.Range("A" & Rows.Count).End(xlUp).Row)
For Each Cel In plage1
P = WorksheetFunction.CountIf(plage2, Cel.Value)
If P = 0 Then f3.Cells(Lig, 2) = f1.Cells(Cel.Row, 1)
If f3.Cells(Lig, 2) <> "" Then Lig = Lig + 1
Next Cel
'*************************************Figure en test2 et non en Test2 *********************************
Lig = 2
For Each Cel In plage2
C = WorksheetFunction.CountIf(plage1, Cel.Value)
If C = 0 Then f3.Cells(Lig, 3) = f1.Cells(Cel.Row, 1)
If f3.Cells(Lig, 3) <> "" Then Lig = Lig + 1
Next Cel
MsgBox ("Controle effectué")
Application.ScreenUpdating = True
f3.Select
End Sub |
Partager