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
| Sub Fonction()
Dim i
Dim safe As Boolean
Dim dernLigne As Integer
With ThisWorkbook.Worksheets("ecran utilisateur")
.Range("C2").Formula = "=archivage!A2"
.Range("F17").Formula = "=3"
.Range("C4").Formula = "=VLookup(C3, archivage!A:J,4,false)"
.Range("C5").Formula = "=VLookup(C3, archivage!A:J,5,false)"
.Range("C6").Formula = "=VLookup(C3, archivage!A:J,6,false)"
.Range("C7").Formula = "=VLookup(C3, archivage!A:J,7,false)"
.Range("C8").Formula = "=VLookup(C3, archivage!A:J,8,false)"
.Range("C9").Formula = "=VLookup(C3, archivage!A:J,9,false)"
.Range("C10").Formula = "=VLookup(C3, archivage!A:J,10,false)"
End With
dernLigne = Cells(Rows.Count, 1).End(xlUp).Row - 1
'___________________________Condition d'activation de la macro __________________________________________
Sheets("ecran utilisateur").Select
If Cells(2, 3) = "" Or Cells(3, 3) = "" Or Cells(4, 3) = "" Or Cells(5, 3) = "" Or Cells(6, 3) = "" Or Cells(7, 3) = "" Or Cells(8, 3) = "" Or Cells(9, 3) = "" Then
Else
For a = 1 To dernLigne
'___________________________transfert de la reference __________________________________________
'Selection
Sheets("archivage").Cells(Rows.Count, 1).End(xlUp)(2).Value = Sheets("ecran utilisateur").Range("C3").Value
Sheets("archivage").Cells(Rows.Count, 2).End(xlUp)(2).Value = Sheets("ecran utilisateur").Range("C13").Value
Sheets("archivage").Cells(Rows.Count, 3).End(xlUp)(2).Value = Sheets("ecran utilisateur").Range("D13").Value
Sheets("archivage").Cells(Rows.Count, 4).End(xlUp)(2).Value = Sheets("ecran utilisateur").Range("E13").Value
Sheets("archivage").Cells(Rows.Count, 5).End(xlUp)(2).Value = Sheets("ecran utilisateur").Range("F13").Value
Sheets("archivage").Cells(Rows.Count, 6).End(xlUp)(2).Value = Sheets("ecran utilisateur").Range("G13").Value
Sheets("archivage").Cells(Rows.Count, 7).End(xlUp)(2).Value = Sheets("ecran utilisateur").Range("H13").Value
Sheets("archivage").Cells(Rows.Count, 8).End(xlUp)(2).Value = Sheets("ecran utilisateur").Range("I13").Value
Sheets("archivage").Cells(Rows.Count, 9).End(xlUp)(2).Value = Sheets("ecran utilisateur").Range("J13").Value
Sheets("archivage").Cells(Rows.Count, 10).End(xlUp)(2).Value = Sheets("ecran utilisateur").Range("K13").Value
Sheets("archivage").Cells(Rows.Count, 11).End(xlUp)(2).Value = Now
'___________________________transfert du statut et du poids enregistre __________________________________________
i = Worksheets("ecran utilisateur").Cells(17, 6).Value
Worksheets("ecran utilisateur").Range("C2").Formula = "=archivage!A" & i
Worksheets("ecran utilisateur").Cells(17, 6).Value = i + 1
Next
End If |