Loop & concatenate Day/Month/Year

[JohnExcel222]

Hi there,
I struggle with some date data, which visually looks like a date, but it is note clean (sorting ascending order does work as text values...)
In Excel, I replace "." with "-" and it solves the problem: the data get converted to a date format.

I have tried to do the same in VBA, some of the data gets converted properly (like... 23.12.2022, recongnised as date data) and some of the data get converted into 23-12-2022, which is not recognesed as a date format.

In Excel, using the excel date function looks like it solves the problem DATE(year,month,day) (I think it is an Excel 365 function).

Can you please help me to find a solution in VBA .... This below does not work (the code has surely some syntax errors ....



Private Function CleanDate(xYr, xMth, xDay) As String
Dim DTcell As Range
'Dim colDT As String
'Dim xYr, xMth, xDay As String

'For Each DTcell In Range("F2:F416")
xYr = Right(DTcell, 4)
xMth = Mid(DTcell, 4, 2)
xDay = Left(DTcell, 2)

CleanDate = Application.WorksheetFunction.Date(xYr, xMth, xDay)

' Next DTcell
End Function

Sub CleanOutput()
Dim DTcell As Range
For Each DTcell In Range("F2:F416")

DTcell = CleanDate()
Next DTcell

End Sub

 

[rlv01]

See if something like this works.

Sub CleanOutput()
    Dim WS As Worksheet
    Dim CellRange As Range
    Dim R As Range
    Dim DT As Variant

    Set WS = ActiveSheet

    With WS
        Set CellRange = .Range("F2", .Range("F" & .Rows.Count).End(xlUp))    'range to last cell in column w/data
    End With

    For Each R In CellRange
        DT = R.Value
        Select Case TypeName(DT)
        Case "Date"
        Case "String"
            R.Value = VBA.DateValue(Replace(DT, ".", "-"))
        End Select
    Next R
End Sub

 

[JohnExcel222]

Hi there, this results in a "Type mismatch error" - Runt time error 13. The code did not work

 

[JohnExcel222]

...actually.... some of the cells do not contain a date.... but this: —
I have deleted the rows with — ...and it worked.
How can we modify the code to cope with this ? Thank you for your help

 

[JohnExcel222]

Sub CleanOutput4()
Dim WS As Worksheet
Dim CellRange As Range
Dim R
Dim DT

Set WS = ActiveSheet

With WS
Set CellRange = .Range("F2", .Range("F" & .Rows.Count).End(xlUp)) 'range to last cell in column w/data
End With

On Error Resume Next
For Each R In CellRange
DT = R.Value
Select Case TypeName(DT)
Case "Date"
Case "String"
R.Value = VBA.DateValue(Replace(DT, ".", "-"))
End Select
Next R
End Sub