PDA

View Full Version : [SOLVED] Change Data from String to Date: Runtime Error '1004'



sophieschrit
10-13-2015, 09:55 AM
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
10-13-2015, 12:13 PM
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
10-13-2015, 01:40 PM
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
10-13-2015, 02:27 PM
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
10-13-2015, 02:51 PM
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
10-13-2015, 03:10 PM
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
10-13-2015, 04:27 PM
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
10-14-2015, 03:52 AM
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? :friends:

p45cal
10-14-2015, 04:11 AM
Where exactly did you change it do Date? :friends: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
10-14-2015, 05:31 AM
Thank you very much. Now I understand it. Brilliant