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

Question

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?

can we see an image of what you want to parse, ie. the text you want to split into columns? — SNicolaou, Nov 04 '19 at 11:35
and what should the end result be? because, textToColumns is used to split a text value (i.e. a single cell/column) into multiple columns. is this what you want to achieve? — SNicolaou, Nov 04 '19 at 11:48
https://imgur.com/a/oyDYyAq I didn't define it cause I don't use 'range' as a variable: it saved a space here. Image: left column after .TextToColumns, right column before any changes. I just want my formula works after copy and paste. — matheonidas, Nov 04 '19 at 12:07
firstly you need to establish what datatype you have in your cells after the copy-paste. Is it a numeric with the wrong display format or is it a text i.e. not possible to change it's format... — SNicolaou, Nov 04 '19 at 12:17
Looking at your right column, if you haven't explicitly set the alignment to be left then your right column values are text, so you need to parse it to convert it into a date and then apply any other formatting, see my answer below. — SNicolaou, Nov 04 '19 at 12:21
it's not a number, it's a text. Cells format is correct(custom: dd/mm/yyyy hh:mm). When I double click cells correct format is adopted never before double click. Sorry I'am new to excel as well as to vba. — matheonidas, Nov 04 '19 at 12:27
It works!!! If I want to apply it for 2500 rows, should I simply iterate through all cells down? — matheonidas, Nov 04 '19 at 13:13
If it works, mark as answered, and if you want to apply to all rows, then yes iterate through all of them, that is one way of doing it — SNicolaou, Nov 04 '19 at 13:20

SNicolaou · Accepted Answer · 2019-11-04T12:29:45.727

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.

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

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

1 Answers1