Search code examples
excelvbatext-to-column

After copy and paste values from one workbook to another, a formula doesn't work with a specific range until I doubleClick cells

Using VBA I open one workbook, copy data and paste it to another workbook. In the second workbook I set a formula for specific range of custom format of values(dd/mm/yyyy hh:mm) but the formula doesn't work. Values are kept on the left side of cells as a text with custom format until I double click. Format in both workbooks(worksheets) is the same and I want to keep it.

I've been trying fix it using:

range.TextToColumns Destination:=range DataType:=xlFixedWidth FieldInfo:=Array(Array(0,9),Array(1,1), Array(16,9))

It works within halfway. Date is fit to the right side of cells but my day from custom date is 17 days less???:D When I omit Array(0,9) it returns additional date 02/01/1990 and my original date is moved to the next column.

Has anyone got any solution? How exactly does .TextToColumns work? How Can I correctly declare arg for FieldInfo to receive original date and fit it to the right side of cells?

Solution

  • below is an example of how to convert a date that is in text format (cell B1) to an actual date. Notice how a text format is left aligned and a date format is right aligned.

    Keep in mind that a date is a number-> the integer part is the date and the decimal part is the time.

    enter image description here

    Public Sub sTestDate()

  Dim v As Variant

  v = Sheet1.Range("B1").Value
  Sheet1.Range("B2").Value = CDbl(CDate(v))
  Sheet1.Range("B3").Value = CDbl(CDate(v))
  Sheet1.Range("B3").NumberFormat = "dd/mm/yyyy hh:mm"

End Sub