VBA Excel - Copying Dates from one row to another changes date format

Question

I need to copy the dates in one column to another. I thought i had done it properly\ however some of the dates change format to USA date format, whilst others remain in the UK format as required for my purpose. Here's what this looks like and the code i use to copy the date, the columns for these dates ar 9 and 11 respectfully.

Sub CopyPlannedDatesToHiddenColumn()

Dim wsCRC As Worksheet
Set wsCRC = Worksheets("CRC")
Dim lrowcrc As Long
lrowcrc = CRC.LastRowInCRC

Dim PlannedDateToCopy As String
Dim i As Integer

For i = 9 To lrowcrc

    PlannedDateToCopy = wsCRC.Cells(i, 9).Value
    wsCRC.Cells(i, 11) = PlannedDateToCopy

Next i

End Sub

Any idea why this is happening and how i could get poast this?

`PlannedDateToCopy` is declared as `string`, so you convert a date value into a string and write a string back into the other cell. Instead try: `wsCRC.Cells(i, 11).Value = wsCRC.Cells(i, 9).Value` so the value is copied and not converted into a string. Also you should [always use `Long` instead of `Integer`](https://stackoverflow.com/a/26409520/3219613) especially when handling with row counts. Excel has more rows than `Integer` can handle. — Pᴇʜ, Jul 20 '17 at 12:57
Change the type of PlannedDateToCopy to Date instead of string. — Vinicius, Jul 20 '17 at 13:04

Pᴇʜ · Accepted Answer · 2017-07-20T13:43:25.157

What is happening?

The issue is that you declared PlannedDateToCopy as type String.

The following line

PlannedDateToCopy = wsCRC.Cells(i, 9).Value

reads the value of the cell as type Date and casts it automatically into a String for the variable.
You then write that String into a new cell …

wsCRC.Cells(i, 11) = PlannedDateToCopy

and this is why the new cell contains a String instead of a Date.

Solution 1: Remove the Variable

The shortest solution would be to write the value directly into the new cell without using a variable:

wsCRC.Cells(i, 11).Value = wsCRC.Cells(i, 9).Value

Solution 2: Declare it the Correct Type

If you explicitly need that variable then declare it …

… as Date and then use .Value

Dim PlannedDateToCopy As Date
PlannedDateToCopy = wsCRC.Cells(i, 9).Value

… or as Long and use .Value2 (see Range.Value2 Property for explaining the difference)
```
Dim PlannedDateToCopy As Long
PlannedDateToCopy = wsCRC.Cells(i, 9).Value2
```

Like @Vinicius and @Scott Craner suggested in their comments.

Additional Note

You should always use Long instead of Integer unless you need to communicate with old APIs. Especially when handling with row counts. Excel has more rows (in newer versions) than Integer can handle:

Dim i As Long

VBA Excel - Copying Dates from one row to another changes date format

1 Answers1

What is happening?

Solution 1: Remove the Variable

Solution 2: Declare it the Correct Type

Additional Note