ERROR Date and Time Text to Columns Macro Excel VBA

Question

I am having trouble coding a macro to separate a cell with date and time information in to columns with TexttoColumns formula. This is the information I have in the column.

Beginning Date

1900-01-01 (00:00)

2008-09-25 (12:46)

I want all the column but the header to be split, with the date in one column and the hour in the next one.

In the macro I have, I am able to find the column I want to split and I create the new column to the right correctly. However, I am not able to do the text to column formula. this is the code I have:

Sub Separate_Date()
  Cells.Find(What:="Fecha Inicio Proceso", After:=Cells(1, 1), 
  LookIn:=xlValues, 
  LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
  MatchCase:=False, SearchFormat:=False).Activate

  Col = ActiveCell.Column
  LastRow = ActiveCell.End(xlDown).Row
  Cells(ActiveCell.Row, ActiveCell.Column + 1).EntireColumn.Insert

  Columns(Col).Select
  Range(Selection, Selection.End(xlDown)).Select

  ********Selection.TextToColumns Destination:=Range(Cells(2, Col), 
  Cells(LastRow, Col)), 
  DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
  Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
  :=" ", FieldInfo:=Array(Array(1, 4), Array(2, 4)), 
  TrailingMinusNumbers:=True

 End Sub

My code stops in the line marked with ****. It shows the error" Run time error. Destination reference is not valid".

I guess my problem is in the Range part, but I haven't been able to find any solution on the website.

score 1 · Answer 1 · answered Jun 10 '17 at 04:51

1

You do not need VBA for this. In few steps you can have what you want. Let's say that the data is in Col A. And if you still want VBA then record a macro for the below steps and simply amend it. :)

Select Col A
Click on Data|Text To Columns|Delimited and select Space as delimiter.

Click Finish
Select Col B. Press CTRL+H. First replace ( with nothing and then replace ) with nothing

Retype the Header in Col A and delete it from Col B

answered Jun 10 '17 at 04:51

Siddharth Rout

147,039
17
206
250

I know I can do it that way. However I need to do this on a weekly basis so I would like to have it automated with a macro so I do not need to do that every week. – Antonio Jun 10 '17 at 11:06
I think you missed the part `And if you still want VBA then record a macro for the below steps and simply amend it. :)....` – Siddharth Rout Jun 10 '17 at 17:07

score 0 · Answer 2 · answered Jun 10 '17 at 02:05

Hopefully, your dates are true dates or can be converted to true dates using the Cdate([date as string]) function. A true date is a number of double type, like 42896.417349537 where the integer is the day and the decimals represent the time. Basically, you can use the exact same number in both your columns but format it differently. "mm-dd-yyyy" will not show the time and "hh:mm:ss" will not show the date.

However, if you wish to separate the values, obviously Int(42896.417349537) will produce a date without time, and 42896.417349537 - Int(42896.417349537) will produce the number representing the time. In both cases you would still control the display via the format, that is the Numberformat property.

Actually, this is all a lot easier on the worksheet than in VBA. Unless it is a recurring task I recommend to do it all with worksheet functions and cell formats.

score 0 · Answer 3 · answered Jun 10 '17 at 02:13

you dont need all that code to separate them... you can just use format$()

try this on a cell:

Public Sub SplitDateTime(lRow As Long, lColumn As Long)

  If IsDate(ActiveSheet.Cells(lRow, lColumn)) Then
    Dim dt As Date
    dt = ActiveSheet.Cells(lRow, lColumn)
    MsgBox "Date: " & Format$(dt, "yyyy-mm-dd") & " Time: " & Format$(dt, "hh:mm:ss")
  Else
    MsgBox "This is not a date cell- Row:" & lRow & " - Column:" & lColumn & " - Value:" & ActiveSheet.Cells(lRow, lColumn)
  End If

End Sub

score 0 · Answer 4 · answered Jun 10 '17 at 15:02

Maybe,

Sub Separate_Date()
Dim rngF As Range, Lastrow As Long, i As Long
Dim Col As Integer
Dim vR(), vSplit, vDB

    Set rngF = Cells.Find(What:="FechaInicio Proceso", LookIn:=xlValues, LookAt:=xlWhole)
    Col = rngF.Column
    Lastrow = rngF.End(xlDown).Row
    rngF.Offset(, 1).EntireColumn.Insert

    vDB = Range(Cells(2, Col), Cells(Lastrow, Col))
    ReDim vR(1 To UBound(vDB, 1), 1 To 2)
    For i = 1 To UBound(vDB, 1)
        vSplit = Split(vDB(i, 1), "(")
        vR(i, 1) = Trim(vSplit(0))
        vR(i, 2) = Trim(Replace(vSplit(1), ")", ""))
    Next i
    Cells(2, Col).Resize(UBound(vR, 1), 2) = vR
    Columns(Col + 1).NumberFormatLocal = "hh:mm"
 End Sub

ERROR Date and Time Text to Columns Macro Excel VBA

4 Answers4