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 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72
| Sub Dates()
For j = 2 To 99999
Cells(j, 10).NumberFormat = "m/d/yyyy"
Cells(j, 11).NumberFormat = "m/d/yyyy"
Cells(j, 12).NumberFormat = "m/d/yyyy"
Next j
FormatDate
End Sub
Sub FormatDate()
Application.ScreenUpdating = False
For i = 1 To Range("A65535").End(xlUp).Row
If Cells(i, 1) Like "##.##/####" Or Cells(i, 1) Like "##/##.####" Or Cells(i, 1) Like "##.##.####" Or Cells(i, 1) Like "##.##.##" Or Cells(i, 1) Like "##.##/##" Or Cells(i, 1) Like "##/##.##" Then
Cells(i, 1).NumberFormat = "@": Cells(i, 1) = Replace(Cells(i, 1), ".", "/"): Cells(i, 1).NumberFormat = "m/d/yyyy"
End If
Next i
RemplacePresse
Application.ScreenUpdating = True
End Sub
Sub RemplacePresse()
On Error Resume Next
Columns("B:B").Replace What:="presse", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
On Error GoTo 0
RemplaceText1
End Sub
Sub RemplaceText1()
On Error Resume Next
Columns("B:B").Replace What:="à chambre fixe", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
On Error GoTo 0
RemplaceText2
End Sub
Sub RemplaceText2()
On Error Resume Next
Columns("B:B").Replace What:="HAUTE DENSITE", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
On Error GoTo 0
RemplaceText3
End Sub
Sub RemplaceText3()
On Error Resume Next
Columns("B:B").Replace What:="Bale Pack", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
On Error GoTo 0
RemplaceText4
End Sub
Sub RemplaceText4()
On Error Resume Next
Columns("B:B").Replace What:="HD", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
On Error GoTo 0
SupEspace
End Sub
Sub SupEspace()
'Dim i As Long
For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
Cells(i, 2) = Trim(WS.Cells(i, 2))
Next i
InterLigne
End Sub
Sub SupInterLigne()
For i = 2 To Range("A60000").End(xlUp).Row
If Range("A" & i).Value = "" Then
Rows(i).Delete
End If
Next i
End Sub |
Partager