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
| Sub Modification()
Dim Ws As Worksheet
Dim LastLig As Long
Dim i As Byte
Dim e As String
Dim h As String
Dim f As Long
Dim t As Long
Dim maSelect As Range
Set maSelect = Sheets("Feuil1").Range("D" & s & ":" & "Q" & t)
Set Ws = Worksheets("Feuil1")
e = Sheet("Feuil1").Range("A2")
h = Sheet("Feuil2").Range("B2")
With Worksheets("BDD")
For i = 3 To 13
.Find.Ws.Range ("C" & i)
Next i
Set c = .Find(e, LookIn:=xlValues)
Set e = .Find(h, LookIn:=xlValues)
If Not c & e & i Is Nothing Then Call enregistrement
Else
.AutoFilterMode = False
LastLig = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("A1:R" & LastLig)
.AutoFilter Field:=1, Criteria1:=Ws.Range("A2")
.AutoFilter Field:=2, Criteria1:=Ws.Range("B2")
For i = 3 To 13
.AutoFilter Field:=3, Criteria1:=Ws.Range("C" & i)
If .Range("A1:A" & LastLig).SpecialCells(xlCellTypeVisible).Count > 1 Then
.Range("D2:Q" & LastLig).SpecialCells(xlCellTypeVisible).Copy.Range ("D" & i)
Else
Ws.Range("D" & i & ":Q" & i).ClearContents
End If
Next i
End With
.AutoFilterMode = False
End With
End If
Set Ws = Nothing
End Sub |
Partager