Change Data from String to Date: Runtime Error '1004'

[sophieschrit]

Hey guys,

I want to convert my Data I have in column C and D from String to Date so I can do my AutoFilter. However I thought the easiest way is to do this with CDate, but every time I get this error: Run-time error '1004': Method 'Range' of Oobject 'Global_Failed' So I assume the problem is that I want to do it with two columns. Has anybody a suggestion how I could fix this problem?
Please find below the macro I'm trying it with and I also attach the workbook.
Thanks a lot

Sub ffff()
Dim PayOutDate As Date
Dim PayOutDate0 As String
Dim Rng1 As Range
Set Rng1 = ActiveSheet.Range("C9:D9").End(xlDown)


PayOutDate0 = Range(Rng1).Value
PayOutDate = CDate(PayOutDate0)




End Sub

[p45cal]

try this macro on the active sheet:

Sub blah()
Set Rng1 = Range(Range("C9"), Range("C9").End(xlDown))
Rng1.TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 4)
Rng1.Offset(, 1).TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 4)
End Sub

[SamT]

Sub String2Date(Rng As Range)
    Dim i As Long
    Dim Temp As Variant
    With Rng
        For i = 1 To .Count
        Temp = Split(.Cells(i), ".")
        Temp = Temp(1) & "-" & Temp(0) & "-" & Temp(2)
            .Cells(i) = CDate(Temp)
        Next i
    End With
End Sub
 
Sub Test()
    String2Date Sheets("Sheet1").Range("C9:D48")
End Sub

[p45cal]

Careful SamT, here in the UK your code changes the date in cell D9 to April 9 2015 instead of 4th September 2015. The dates in the 2 columns are usually only about 5 days apart.

[SamT]

Thanks for the tip.

I changed it to

Sub String2Date(Rng As Range)
    Dim i As Long
    Dim Temp As Variant
    With Rng
        For i = 1 To .Count
        Temp = Split(.Cells(i), ".")
        Temp = Temp(1) & "-" & Temp(0) & "-" & Temp(2)
            .Cells(i) = CDate(Temp)
        Next i
    End With
End Sub
 
Sub Test()
    String2Date Sheets("Sheet1").Range("C9:D48")
End Sub

How's it work now?

[p45cal]

How's it work now?

Well… contrary to expectations it did the same thing! However, trying:
Temp = Temp(2) & "-" & Temp(1) & "-" & Temp(0)
worked (here in the UK).

[SamT]

IIRC, snb said some thing about yyyy-mm-dd being universal. I get so used to entering dmmmyy in cells that I forget these things.

Nice thing about using Split on dates. If one arrangement doesn't work, you only have to change 3 numbers

[sophieschrit]

Thanks a million this works perfectly. Would be great if you could explain me how you did so I can understand it better. Where exactly did you change it do Date?

[p45cal]

Where exactly did you change it do Date?

Rng1.TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 4)
is the same as (after selecting cells in a single column):
In the ribbon, choosing the Data tab and clicking Text to Columns then
in step 1 of the dialogue box that comes up choosing Delimited, clicking Next
in step 2 clearing all checkboxes and fields (Text qualifier doesn't matter), clicking Next
in step 3 choosing Date and in the dropdown next to that choosing DMY (that's DayMonthYear (telling Excel what order your values are in) then clicking Finish.

[sophieschrit]

Thank you very much. Now I understand it. Brilliant