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 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
|
Sub RECHERCHVapplique2()
Dim Repertoire As String
'Repertoire = Sheets("Données").Range("requete").Cells(1, 1)
Repertoire = "C:\Users\....\Documents\suivi\requêtes\courrier\"
Application.ScreenUpdating = False
Application.EnableEvents = False
MsgBox Repertoire
Dim FichierSource As String
Dim FichierDest As Workbook
Dim FeSource As Worksheet
Dim FeDest As Worksheet
Dim PlgSource As Range
Dim PlgSourceAnnee As Range
Dim PlgDest As Range
Dim PlgDestAnnee As Range
Dim cel As Range
'Dim cel2 As Range
'défini les fichier
FichierSource = Dir(Repertoire & "*.xlsx")
Set FichierDest = ActiveWorkbook
'défini les feuilles
Set FeDest = FichierDest.Worksheets("redevance card 2017")
With FeDest
Set PlgDest = .Range(.Cells(2, 7), .Cells(.Rows.Count, 7).End(xlUp))
Set PlgDestAnnee = .Range(.Cells(2, 7), .Cells(.Rows.Count, 7).End(xlUp))
End With
Workbooks.Open Repertoire & FichierSource
'Set FeSource = FichierSource.Sheets(1)
'défini les plages
Set PlgSource = Sheets(1).Range(Cells(2, 3), Cells(Rows.Count, 3).End(xlUp))
Set PlgSourceAnnee = Sheets(1).Range(Cells(2, 22), Cells(Rows.Count, 22).End(xlUp))
'parcour la plage (en colonne A)
For Each cel In PlgDest
If IsError(Application.Match(cel.Value, PlgSource, 0)) = False And IsError(Application.Match(cel.Offset(0, 5).Value, PlgSourceAnnee, 0)) = False Then
cel.Offset(0, 6).Value = "oui"
End If
Next cel
End Sub |
Partager