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
| Sub Test_Update()
Dim i, j, x, y As Integer
Dim mdt, dte, cell, cel, ce As Range
Dim match As Date
match = DateSerial(Year(Date), Month(Date) + 1, 0)
'-----------------------------------------------------
Sheets(1).Activate 'Sur le Sheets(1): la BDD
For Each cell In Range("B4:B50") 'Pour chaque cellule du range de la BDD
Sheets(1).Activate 'Sur le Sheets(1): la BDD
i = cell.Row 'i est le numéro de ligne
Range(Cells(i, 5), Cells(i, 9)).Select 'On passe le range de chaque mandat
Selection.Copy 'On copie le range
'-----------------------------------------------------
Sheets(2).Activate 'Sur le Sheets(2): le rapport
Set ce = Range("C6:ON6")
ce.Select
Set cel = Range("B8:B80")
cel.Select
Set mdt = ce.Find(cell.Value, LookIn:=xlFormulas)
Set dte = cel.Find(match, LookIn:=xlFormulas)
If Not mdt Is Nothing Then
cell.Value = mdt.Value
Else
If dte Is Nothing Then
MsgBox dte & " non trouve dans " & cel.Address
Else
Range(Cells(dte.Row, mdt.Row), Cells(dte.Row, mdt.Row + 5)).Select
Selection.Insert
End If
End If
Next
End Sub |
Partager