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
| Sub essai()
Dim Ws1 As Worksheet, Ws2 As Worksheet, Wresult As Worksheet
Dim dl As Long, x As Long, y As Long, i As Long
Dim Tb1, Tb2, Tbresult()
Set Ws1 = Workbooks("base1.xlsm").Sheets("Feuil1") 'à adapter
Set Ws2 = Workbooks("base2.xlsm").Sheets("Feuil1") 'à adapter
Set Wresult = Workbooks("resultat.xlsm").Sheets("Feuil1") 'à adapter
With Ws1
dl = .Range("A" & .Rows.Count).End(xlUp).Row 'si les données sont en A et B
Tb1 = .Range("A2:B" & dl)
End With
With Ws2
dl = .Range("A" & .Rows.Count).End(xlUp).Row 'si les données sont en A et B
Tb2 = .Range("A2:B" & dl)
End With
i = 0
ReDim Preserve Tbresult(1 To UBound(Tb1, 1) + UBound(Tb2, 1), 1 To 4)
dl = WorksheetFunction.Min(UBound(Tb1, 1), UBound(Tb2, 1))
For x = 1 To WorksheetFunction.Max(UBound(Tb1, 1), UBound(Tb2, 1))
If dl >= x Then
If Tb1(x, 1) = Tb2(x, 1) And Tb1(x, 2) <> Tb2(x, 2) Then
i = i + 1
Tbresult(i, 1) = Tb1(x, 1)
Tbresult(i, 2) = Tb1(x, 2)
Tbresult(i, 3) = Tb2(x, 1)
Tbresult(i, 4) = Tb2(x, 2)
ElseIf Tb1(x, 1) <> Tb2(x, 1) Then
i = i + 1
Tbresult(i, 1) = Tb1(x, 1)
Tbresult(i, 2) = Tb1(x, 2)
i = i + 1
Tbresult(i, 3) = Tb2(x, 1)
Tbresult(i, 4) = Tb2(x, 2)
End If
ElseIf x < UBound(Tb1, 1) Then
i = i + 1
Tbresult(i, 1) = Tb1(x, 1)
Tbresult(i, 2) = Tb1(x, 2)
ElseIf x < UBound(Tb2, 1) Then
i = i + 1
Tbresult(i, 3) = Tb2(x, 1)
Tbresult(i, 4) = Tb2(x, 2)
End If
Next x
Wresult.UsedRange.ClearContents
Wresult.Range("A2:D" & UBound(Tbresult, 1) + 1) = Tbresult
End Sub |
Partager