Excel VBA following script not converting all dates correctly

Question

I have the following script from another question that is helping me to split values in to a new column and convert text to dates

Dim StartString As String
Dim DateValue As String
Dim y As Integer

Dim LastRow2 As Long
With Sheets("DataSheet")
LastRow2 = .Cells(.Rows.Count, "L").End(xlUp).Row 'find the last row on column L
    .Columns(13).Insert Shift:=xlToRight 'add a new column to the right of column L
    For i = 1 To LastRow2 'loop through rows
       If InStr(1, .Cells(i, "L"), ",") Then
            .Cells(i, "M").Value = Split(.Cells(i, "L"), ",")(1) 'split after comma
            StartString = .Cells(i, "L").Value
            DateValue = ""
            For y = 1 To Len(StartString) 'loop to remove unwanted characters
                Select Case Asc(Mid(StartString, y, 1))
                    Case 47 To 57
                        DateValue = DateValue & Mid(StartString, y, 1)
                End Select
            Next y
        .Cells(i, "M").Value = DateValue 'return the date
        .Cells(i, "M").NumberFormat = "dd/mm/yyyy" 'format it correctly
        End If
    Next i
End With

The issue I have is that the conversion is not successful in all cases. This causes an issue in the next stage of my code as it uses the new dates as column headers that must be chronologically sorted. Any help is appreciated!

Below are also results (please ignore cells in yellow as this input error) The cells in green seem to have been converted successfully but you can see that many other the other cells have the small green error indicator in the top left corner. :

It seems that you have dates from the time when Tiberius Claudius Nero ruled the Roman Empire and when 'milestones' were stones actually. — MarcinSzaleniec
– MarcinSzaleniec, Commented Dec 15, 2017 at 14:21

Scott Holtzman · Accepted Answer · 2017-12-19 14:35:18Z

1

I suggest the below code that may be easier to process and understand and most importantly, work as tested on data similar to what you provided in your example :)

Option Explicit

Sub ExtractDate()

    Dim DateValue As String, FinalDate As String
    Dim I As Integer

    Dim LastRow2 As Long
    With Sheets("DataSheet")

        LastRow2 = .Cells(.Rows.Count, "L").End(xlUp).Row 'find the last row on column L
        .Columns(13).Insert Shift:=xlToRight 'add a new column to the right of column L

        For I = 1 To LastRow2 'loop through rows

           If InStr(1, .Cells(I, "L"), ",") Then

                DateValue = Split(.Cells(I, "L"), ",")(1) 'split after comma

                If IsNumeric(Left(DateValue,2)) Then 

                    DateValue = Split(DateValue, "/")(1) & "/" & Split(DateValue, "/")(0) & "/" & Split(DateValue, "/")(2)
                    FinalDate = CDate(DateValue)

                    .Cells(I, "M").Value = Format(FinalDate, "dd/mm/yyyy")
                 End If
            End If

        Next I

    End With

End Sub

edited Dec 19, 2017 at 14:35

answered Dec 15, 2017 at 15:27

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

SDROB Over a year ago

it fails with the lines that have the Milestone keyword but text after it instead of a date. And in other cases the days and month digits are switched

Scott Holtzman Over a year ago

@SDROB - the first point can be fixed easily enough (see my edit for that). For the second part, can you explain in more detail? Are you saying as a whole, days / months are in reverse order? Or only on some lines? If it's only on same lines, you have a bigger problem in terms of how dates are entered as they are inconsistent.

SDROB Over a year ago

It will only take values as dd/mm/yy, when the first values "dd" are 12 or less. Anything with the first two digits greater than 12 is not converted to a numerical and remains as text. I dont understand why when the final date is formatted with .Cells(I, "M").Value = Format(FinalDate, "dd/mm/yyyy")

Scott Holtzman Over a year ago

@SDROB - Did you copy my code into your program exactly as is? I am confused, since all my examples have a days > 12. Also notice how I build DateValue in mm/dd/yyyy format so that CDate can evaluate it as a date (I am pretty sure VBA date functions need it in mm/dd/yyyy format to work.

SDROB Over a year ago

Yes exactly as is.. What I find is that the cells will only update to dates (or move to the left side of the cell), after the macro has run and once I double click on the cell. I tried to include the line .Cells(Z, "M").DoubleClick int he line below .Cells(Z, "M").Value = Format(FinalDate, "dd/mm/yyyy") but this has not helped. Any suggestions on this?

Xabier · Accepted Answer · 2017-12-15 14:44:38Z

0

I believe I found the culprit, try replacing your code with the following:

Sub foo()
Dim StartString As String
Dim DateValue As String
Dim y As Integer
Dim LastRow As Long
With Sheets("Datasheet")
LastRow = .Cells(.Rows.Count, "L").End(xlUp).Row
    .Columns(13).Insert Shift:=xlToRight
    For i = 1 To LastRow
        If InStr(1, .Cells(i, "L"), ",") Then
            .Cells(i, "M").Value = Split(.Cells(i, "L"), ",")(1)
            StartString = .Cells(i, "L").Value
            DateValue = ""
            For y = 1 To Len(StartString)
                Select Case Asc(Mid(StartString, y, 1))
                    Case 47 To 57
                        DateValue = DateValue & Mid(StartString, y, 1)
                End Select
            Next y
        .Cells(i, "M").Value = DateValue
        End If
        If .Cells(i, "M").Value <> "" Then
            .Cells(i, "M").Value = CDate(.Cells(i, "M").Value)
            .Cells(i, "M").Value = Format(.Cells(i, "M").Value, "dd/mm/yyyy")
        End If
    Next i
End With
End Sub

answered Dec 15, 2017 at 14:44

Xabier

7,7251 gold badge11 silver badges21 bronze badges

6 Comments

SDROB Over a year ago

I get a run time-error 13 type mismatch on the following line .Cells(i, "M").Value = CDate(.Cells(i, "M").Value)'

Xabier Over a year ago

Can you try this by commenting out the Option Explicit line at the top? Let me know if you come across any other issues, but again this worked for me,...

SDROB Over a year ago

which Option Explicit line? I do not see that

Xabier Over a year ago

The error you are getting is because one of your variables is declared with a mismatched type, so if you have a line right at the top of your code that says Option Explicit, by commenting that out, your variables don't necessarily have to be declared, and as such it might get rid of the error, if not, then another way is to check which variable is generating the error, and then change its type when you Dim it...

SDROB Over a year ago

.Cells(i, "M").Value = CDate(.Cells(i, "M").Value)' but this line has no variable as such where I can see that the error would arise? The i is merely a counter?

|

Collectives™ on Stack Overflow

Excel VBA following script not converting all dates correctly

2 Answers 2

5 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related