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
| Option Explicit
Sub DJSMKBI()
Dim L As Integer, C As Integer, Cpt As Integer, Ld As Integer, Cd As Integer
Dim CptDate As Integer, NBRLIGNES As Integer, ID_agent As Range
Ld = 2
Cd = 3
L = 2
C = 3
Set ID_agent = Range("A2", [A2].End(xlDown))
NBRLIGNES = ID_agent.Count
Cells(L, C).Select
Cells(L, C).Value = Application.InputBox("Saisir une date sous forme JJ MMMM ")
'copie de la date dans la colonne date
Cells(Ld, Cd).Select
Selection.Copy
Application.ScreenUpdating = False
For CptDate = 1 To NBRLIGNES
Selection.Copy
Cells(Ld, Cd).Select
ActiveSheet.Paste
Ld = Ld + 1
Next CptDate
Application.ScreenUpdating = False
'traitement des formules date
Cells(L, C + 1).FormulaR1C1 = "=TEXT(RC[-1],""jjjj"")"
Cells(L, C + 2).FormulaR1C1 = "=NO.SEMAINE(RC[-2],2)"
Cells(L, C + 3).FormulaR1C1 = "=TEXT(RC[-3],""mmmm"")"
Range(Cells(L, C + 1), Cells(L, C + 3)).AutoFill Range(Cells(L, C + 1), Cells(L + NBRLIGNES - 1, C + 3))
L = L + 1
Application.CutCopyMode = False
Application.ScreenUpdating = False
End Sub |