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
| Sub Completer()
Dim WbS As Workbook, WbC As Workbook
Dim WsS As Worksheet, WsC As Worksheet
Dim Cel As Range, C As Range
Dim firstAddress As String
'Fichier source
Set WbS = Workbooks("Fichier 1 complété par différentes personnes.xls")
Set WsS = WbS.Worksheets("41 - Balance brute avec montant")
'Fichier Cible
Set WbC = Workbooks("Fichier 2 à compléter avec le 1.xls")
Set WsC = WbC.Worksheets("41 - Balance brute avec montant")
For Each Cel In WsS.Range("E2:E" & WsS.Range("A" & Rows.Count).End(xlUp).Row)
'WsS.Range("A" & Rows.Count).End(xlUp).Row : retourne la valeur 41 et permet de parcourir tout le tableau source
If Application.CountA(Cel.Resize(1, 3)) > 0 Then
'Recherche du code article
Set C = WsC.Columns(3).Find(Cel.Offset(0, -2), , xlValues, xlWhole)
If Not C Is Nothing Then
firstAddress = C.Address
Do
'Contrôle de concordance du Code entité IN et RG Siège
If Cel.Offset(0, -4) = C.Offset(0, -2) And Cel.Offset(0, -3) = C.Offset(0, -1) Then
Cel.Resize(1, 3).Copy C.Offset(0, 9)
Exit Do
End If
Set C = WsC.Columns(3).FindNext(C)
Loop While Not C Is Nothing And C.Address <> firstAddress
End If
End If
Next Cel
Set C = Nothing: Set WsC = Nothing: Set WsS = Nothing: Set WbC = Nothing: Set WbS = Nothing
End Sub |
Partager