CODEWITHSUNDEEP
excelvba
jamheadart
jamheadart

Reputation: 5313

Excel VBA date copying different for no apparent reason?

Simple task with confusing alternate results.

I'm copying a range of data using:

WS.Range("A2:Z" & lRow).Copy
ThisWorkbook.Worksheets("Import").Range("A2:Z" & lRow).PasteSpecial xlPasteValues

The first column from the copy sheet is a date in format 12/05/2017 01:00:00 (note the double space in between date and time)

In one instance of this the date values are pasted across fine and come out as dates - great!

In another instance of this the date values are pasted but come out as 14/05/2017 01:00 and these aren't registering as dates, rather as a text string.

I noticed I could go through the dates cell by cell and press enter which converted them to dates, so I tried using .range("A1:A100").value = .range("A1:A100").value to no avail.

I suspect it may have something to do with the day-month-year format as opposed to being month-day-year (since it works for a sheet that starts on 12-may but not on 14-may) but (1) could there be another difference, (2) why does pressing ENTER work fine and (3) how can I emulate pressing ENTER on my whole range of cells (bearing in mind .value = .value doesn't work)

Upvotes: 0

Views: 397

Answers (1)

Vegard
Vegard

Reputation: 4917

In short: Type conversion (fixing the values before they cause trouble on your worksheet in the first place) is absolutely the best way to go.

In long:

Let's start with these two values:

22.05.2017 12:00
22.05.2017  12:00

I'll place the first in A1 and the second in B1. Note that Excel will often try to do the type conversion, so in this case I'll manually enforce A1 to contain text values by formatting the cell as such after-the-fact.

Let's verify:
enter image description here

Using the Immediate window, we can see that the compiler recognizes the content of A1 as a pure text value, while it recognizes the content in B1 as a date value.

The solution you need is to ensure that any text values are converted to date values:

Option Base 1

Sub pasteTextAsDate()
    Dim dateArr As Variant
    Dim rng As Range

    Set rng = ThisWorkbook.Worksheets(1).Range("A1:A3")

    ' In the line below, we fill the variant variable with the content of the range, casting the variable into an array of variants
    dateArr = rng

    ' For the sake of proving this code works, we'll start by printing the content and what type it is
    For Each s In dateArr
        Debug.Print s & " - " & TypeName(s)
    Next s

    For i = 1 To UBound(dateArr)
        ' This is where we loop through the array and cast any string values to date values
        dateArr(i, 1) = CDate(dateArr(i, 1))

        ' Here we verify for ourselves that the conversions are OK
        Debug.Print dateArr(i, 1) & " - " & TypeName(dateArr(i, 1))
    Next i

    ' And here we print the result to the worksheet
    ThisWorkbook.Worksheets(1).Range("C1:C3").Value = dateArr
End Sub

Result: enter image description here

Upvotes: 1

Related Questions