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
| Sub appel1()
Dim x As Long, Tb1, Tb2, Tbtemp(), TbResult(), DerLg As Range
Dim u As Long, CelGardee As Integer
Dim y As Long, DerCol As Range
'j'imagine un tableau en Feuil1 (A à E)
'un tableau en Feuil2 (A à E)
'le resultat en Feuil3 (A à E)
'les valeurs à comparer en A
CelGardee = InputBox("combien de colonnes à garder")
With Sheets("Feuil1")
Set DerLg = .Range("A" & .Rows.Count).End(xlUp)
Tb1 = .Range("A2", DerLg(1, 5)) 'si lignes de titre en ligne 1
End With
With Sheets("Feuil2")
Set DerLg = .Range("A" & .Rows.Count).End(xlUp)
Tb2 = .Range("A2", DerLg(1, 5)) 'si lignes de titre en ligne 1
End With
u = 0
For x = 1 To UBound(Tb1, 1)
For y = 1 To UBound(Tb2, 1)
If Tb1(x, 1) = Tb2(y, 1) Then
u = u + 1
ReDim Preserve Tbtemp(1 To u)
Tbtemp(u) = Tb1(x, 1) & ";" & Tb1(x, 2) & ";" & Tb1(x, 3) & ";" & Tb1(x, 4) & ";" & Tb1(x, 5)
End If
Next y
Next x
ReDim TbResult(1 To UBound(Tbtemp), 1 To CelGardee)
For x = 1 To UBound(Tbtemp)
For y = 1 To CelGardee
TbResult(x, y) = Split(Tbtemp(x), ";")(y - 1)
Next y
Next x
With Sheets("Feuil3")
Set DerCol = .Cells(1, .Columns.Count).End(xlToLeft)
Set DerLg = .Cells(.Rows.Count, DerCol.Column).End(xlUp)
If DerLg.Row > 1 Then .Range("A1", DerLg(1, DerCol.Column)).ClearContents
If DerCol.Column > CelGardee Then .Range(Cells(1, CelGardee + 1), Cells(1, DerCol.Column)).ClearContents
.Range("A2").Resize(UBound(TbResult, 1), CelGardee) = TbResult
For x = 1 To CelGardee
.Cells(1, x) = Sheets("Feuil1").Cells(1, x)
Next x
End With
Erase Tbtemp
End Sub |
Partager