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
|
Option Explicit
Sub MettreAJourClasseur2()
Dim FichierSource As Workbook
Dim AireSource As Range, CelluleSource As Range, AireCible As Range, CelluleCible As Range
Dim DerniereLigne As Long
Dim Repertoire As String
Dim CorrespondanceTrouvee As Boolean
' Repertoire = "C:\Users\Eric\Downloads\"
Repertoire = "E:\Nounours\Maxwell_W\"
Set ShCible = Sheets("Feuil1")
With Sheets("Feuil1")
DerniereLigne = .Cells(.Rows.Count, 1).End(xlUp).Row
Set AireCible = .Range(.Cells(1, 1), .Cells(DerniereLigne, 1))
AireCible.Offset(0, 1).ClearContents
End With
Set FichierSource = Workbooks.Open(Filename:=Repertoire & "Classeur1.xlsx")
With FichierSource.Sheets("Feuil1")
DerniereLigne = .Cells(.Rows.Count, 1).End(xlUp).Row
Set AireSource = .Range(.Cells(1, 1), .Cells(DerniereLigne, 1))
End With
For Each CelluleCible In AireCible
CorrespondanceTrouvee = False
For Each CelluleSource In AireSource
If CelluleCible = CelluleSource Then
CelluleCible.Offset(0, 1) = CelluleSource.Offset(0, 1)
CorrespondanceTrouvee = True
Exit For
End If
Next CelluleSource
If CorrespondanceTrouvee = False Then
CelluleCible.Offset(0, 1) = "Pas de correspondance"
End If
Next CelluleCible
FichierSource.Close savechanges:=False
Set AireCible = Nothing
Set AireSource = Nothing
Set FichierSource = Nothing
Set ShCible = Nothing
End Sub |
Partager