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
|
Option Explicit
Sub MiseAJourColonneU()
Dim I As Long, Derniereligne As Long
Dim dat As Variant
Dim AirePlanning As Range
With Sheets("Planning")
Derniereligne = .Cells(.Rows.Count, 21).End(xlUp).Row
Set AirePlanning = .Range(.Cells(4, 21), .Cells(Derniereligne, 21))
For I = 1 To AirePlanning.Count
With AirePlanning(I)
If .Value = "Yes" Then
dat = Application.InputBox("Please enter the expiry date of your HSE document", , Format(Date, "DD/MM/YYYY"))
If IsDate(dat) Then
.Offset(0, 1).Value = Format(dat, "MM/DD/YYYY") ' A adapter si le le format ne convient pas.
Else
.Offset(0, 1).ClearContents ' En toute logique
End If
.Value = "No"
End If
End With
Next I
Set AirePlanning = Nothing
End With
End Sub |
Partager