Oct 11 2021 02:59 AM
Good Morning
I need to make a macro to fix all the Date entries that are entered. Some dates are entered with 10/01/2021" others "01-10-2021" or "01.10.2021".
With this routine, when opening the file, all dates are reset to the slashes "/".
The routine works but is quite slow. Is there another way of faster programming to do?
Thank you for your help
Sub Formatar_Datas()
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim Cell As Range
Dim Cont As Long
Dim ContBD As Integer
Dim Arr1 As Variant
Arr1 = Array("J4:J", "Q4:Q", "R4:R", "S4:S", "T4:T", "U4:U", "V4:V", "W4:W", "X4:X", "Y4:Y", "Z4:Z", "AA4:AA", "AG4:AG", "AI4:AI", "AK4:AK", "AT4:AT", "AV4:AV", "AW4:AW", "AZ4:AZ", "BB4:BB", "BD4:BD", "BM4:BM", "BO4:BO")
ContBD = 1
For Cont = LBound(Arr1) To UBound(Arr1)
For Each Cell In Folha3.Range(Arr1(Cont) & Folha3.Cells(Folha3.Rows.Count, "C").End(xlUp).Row)
If IsDate(Cell.Value) = False Then Cell.Value = ""
If Len(Cell.Value) = 10 Then
Cell.Value = Replace(Cell.Value, ".", "/")
Cell.Value = Replace(Cell.Value, "-", "/")
Cell.Value = Replace(Cell.Value, "_", "/")
End If
Next Cell
ContBD = ContBD + 1
Next Cont
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Oct 11 2021 03:38 AM - edited Oct 11 2021 03:39 AM
SolutionThis should be faster:
Sub Formatar_Datas()
Dim m As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
m = Folha3.Range("C" & Folha3.Rows.Count).End(xlUp).Row
With Range("J4:J" & m & ",Q4:Q" & m & ",R4:R" & m & ",S4:S" & m & ",T4:T" & m & _
",U4:U" & m & ",V4:V" & m & ",W4:W" & m & ",X4:X" & m & ",Y4:Y" & m & _
",Z4:Z" & m & ",AA4:AA" & m & ",AG4:AG" & m & ",AI4:AI" & m & ",AK4:AK" & m & _
",AT4:AT" & m & ",AV4:AV" & m & ",AW4:AW" & m & ",AZ4:AZ" & m & _
",BB4:BB" & m & ",BD4:BD" & m & ",BM4:BM" & m & ",BO4:BO" & m)
.Replace What:=".", Replacement:="/"
.Replace What:="-", Replacement:="/"
.Replace What:="_", Replacement:="/"
End With
'Application.Calculation = xlCalculationautomatic
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
But are all dates in ddmmyyyy order? Or all in mmddyyyy order? If there is a mixture, you have a problem...
Oct 11 2021 04:11 AM
Oct 11 2021 05:37 AM
The code is so fast because it does not loop through the cells. It uses the Replace method of the range to act on all cells in one go.
Oct 11 2021 07:12 AM - edited Oct 11 2021 07:14 AM
@Hans Vogelaar A strange thing is happening, if you see in the attached image, the cell is formatted as Date "/", but in the visible part of the cell is the date with "-", but if we place the mouse on the cell, we can see that it is recorded as "/".
When running the code below, visually, the cell always has the "-".
Dim m As Long
m = Folha3.Range("C" & Folha3.Rows.Count).End(xlUp).Row
With Range("AT4:AT" & m)
.Replace What:=".", Replacement:="/"
.Replace What:="-", Replacement:="/"
.Replace What:="_", Replacement:="/"
End With
Why is this happening?
Oct 11 2021 07:53 AM
@csoares Select from A4 down to the last used row,
Set the number format to dd/mm/yyyy (or its equivalent in Portuguese)
Oct 11 2021 08:32 AM
Playing with 365 insider beta, the following worksheet function creates dates.
= --REDUCE(dates, {"-",".","_"}, LAMBDA(acc,s, SUBSTITUTE(acc,s,"/")))
returns serial numbers for the array 'dates' in any of the formats you mention. Alternatively, one can introduce a named Lambda function
"REPLACEλ"
= LAMBDA(acc,s, SUBSTITUTE(acc, s, "/"))
"within the worksheet formula"
= --REDUCE(dates, {"-",".","_"}, REPLACEλ)
This is just 'for information'. The solution you need was the VBA with the formula applied to the entire range.
Oct 12 2021 05:25 AM
Oct 12 2021 06:47 AM
Oct 12 2021 12:09 PM
Try this:
Sub EraseSomeText()
With Range("E:E")
.AutoFilter Field:=1, Criteria1:="<>PT*"
.ClearContents
.AutoFilter
End With
End Sub
Change E:E to the relevant column.
Oct 11 2021 03:38 AM - edited Oct 11 2021 03:39 AM
SolutionThis should be faster:
Sub Formatar_Datas()
Dim m As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
m = Folha3.Range("C" & Folha3.Rows.Count).End(xlUp).Row
With Range("J4:J" & m & ",Q4:Q" & m & ",R4:R" & m & ",S4:S" & m & ",T4:T" & m & _
",U4:U" & m & ",V4:V" & m & ",W4:W" & m & ",X4:X" & m & ",Y4:Y" & m & _
",Z4:Z" & m & ",AA4:AA" & m & ",AG4:AG" & m & ",AI4:AI" & m & ",AK4:AK" & m & _
",AT4:AT" & m & ",AV4:AV" & m & ",AW4:AW" & m & ",AZ4:AZ" & m & _
",BB4:BB" & m & ",BD4:BD" & m & ",BM4:BM" & m & ",BO4:BO" & m)
.Replace What:=".", Replacement:="/"
.Replace What:="-", Replacement:="/"
.Replace What:="_", Replacement:="/"
End With
'Application.Calculation = xlCalculationautomatic
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
But are all dates in ddmmyyyy order? Or all in mmddyyyy order? If there is a mixture, you have a problem...