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
| Dim madate As Variant
Dim vdate As Variant
' Plages de dates à formater
monarray = Array(ActiveWorkbook.Sheets("Demands").Range("I2:L" & dernLigneD), _
ActiveWorkbook.Sheets("Demands").Range("Z2:AH" & dernLigneD), _
ActiveWorkbook.Sheets("Projects").Range("L2:M" & dernLigneP), _
ActiveWorkbook.Sheets("Projects").Range("O2:X" & dernLigneP), _
ActiveWorkbook.Sheets("Projects").Range("AC2:AK" & dernLigneP))
For i = 0 To UBound(monarray) ' Pour chaque élément de l'array
Set maplage = monarray(i) ' Déclaration de la valeur
' Formatage des dates
For Each cell In maplage.Cells
If Len(cell.Value) > 0 Then ' Si la cellule n'est pas vide
vdate = cell.Value
' If IsDate(vdate) = False Then
vdate = Trim(vdate) ' Nettoyage d'éventuels espaces excédentaires
vdate = Replace(vdate, " Jan ", "/01/") ' Table de correspondance entre les
vdate = Replace(vdate, " Feb ", "/02/") ' mois abrégés US -> numéro de mois
vdate = Replace(vdate, " Mar ", "/03/")
vdate = Replace(vdate, " Apr ", "/04/")
vdate = Replace(vdate, " May ", "/05/")
vdate = Replace(vdate, " Jun ", "/06/")
vdate = Replace(vdate, " Jul ", "/07/")
vdate = Replace(vdate, " Aug ", "/08/")
vdate = Replace(vdate, " Sep ", "/09/")
vdate = Replace(vdate, " Oct ", "/10/")
vdate = Replace(vdate, " Nov ", "/11/")
vdate = Replace(vdate, " Dec ", "/12/")
vdate = Replace(vdate, "January ", "01/01/") ' Table de correspondance entre les
vdate = Replace(vdate, "February ", "01/02/") ' mois complets US -> numéro de mois
vdate = Replace(vdate, "March ", "01/03/")
vdate = Replace(vdate, "April ", "01/04/")
vdate = Replace(vdate, "May ", "01/05/")
vdate = Replace(vdate, "June ", "01/06/")
vdate = Replace(vdate, "July ", "01/07/")
vdate = Replace(vdate, "August ", "01/08/")
vdate = Replace(vdate, "September ", "01/09/")
vdate = Replace(vdate, "October ", "01/10/")
vdate = Replace(vdate, "November ", "01/11/")
vdate = Replace(vdate, "December ", "01/12/")
madate = CDate(vdate) ' conversion du résultat au format Date préféré
cell.Value = madate
' End If
madate = Format(madate, "dd/mm/yyyy;@", vbMonday)
' cell.Value = madate
Else
' on ignore le contenu
End If
Next cell
Next i |
Partager