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
| Option Explicit
Sub test()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Long, j As Long, maxLig1 As Long, maxLig2 As Long, maxCol As Long, ligne As Long
Dim rng1 As Range, rng2 As Range, search As Range, adr As Range
Dim firstAddress
maxLig1 = Feuil1.Range("A" & Rows.Count).End(xlUp).Row 'Récupère l'indice de la dernière ligne remplie de Feuil1
maxLig2 = Feuil2.Range("A" & Rows.Count).End(xlUp).Row 'Idem pour Feuil2
With Feuil2
maxCol = .Range(.Cells(1, 1), .Cells(Rows.Count, Columns.Count)).Find("*", , , , xlByColumns, xlPrevious).Column
Set rng2 = .Range(.Cells(1, 1), .Cells(maxLig2, maxCol))
End With
With Feuil1
Set rng1 = .Range(.Cells(1, 1), .Cells(maxLig1, maxCol + 1))
End With
For i = 1 To maxLig2
Set search = rng1.Find(rng2.Cells(i, 1).Value, , , xlPart, , , False)
If Not search Is Nothing Then
firstAddress = search.Address
Do
ligne = search.Row
If InStr(rng1.Cells(ligne, 1).Value, rng2.Cells(i, 2).Value) Then
For j = 2 To maxCol + 1
rng1.Cells(ligne, j).Value = rng2.Cells(i, j - 1).Value
Next j
End If
Set search = rng1.FindNext(search)
Loop While Not search Is Nothing And search.Address <> firstAddress
End If
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub |
Partager