CODEWITHSUNDEEP
excelvba
m.swiss
m.swiss

Reputation: 104

Date value changes when copied

I have cells containing a simple concatenation function with D24 as the previous year (e.g. 15) and a custom format (MMM JJ)

CONCATENATE("Apr ",$D$24)

When I am copying and pasting these cells with a VBA then "Apr 15" becomes "15.04.16" and because of the formatting "Apr 16"

Selection.Value = Selection.Value

Was is the reason behind this? Is there another solution than just changing the format to text?

Upvotes: 1

Views: 1717

Answers (3)

Mátray Márk
Mátray Márk

Reputation: 466

If you simply Copy Paste it, only the Value is pasted not the formatting (if I remember right)

Try to avoid using Selection instead use Range.

And use Range.Copy and Range.PasteSpecial Paste:=xlPasteFormats so your formatting is pasted with the values.

Upvotes: 0

Jochen
Jochen

Reputation: 1254

That depends on what you want in your cell. Dou you want a string or a date?

If you want a string:

  • either format as text or

  • add a ': CONCATENATE("'Apr ",$D$24)

if you want a date:

  • use the following formula instead of concatenate: =DATE($D$24,4,1)

Upvotes: 2

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

Excel will generally try to convert anything that looks like a date into a real date (serial number where 1 = 1 Jan 1900). One way to avoid that, and remove the formula as you are doing above, would be to pre-format as text. So:

With Selection
    .NumberFormat = "@"
    .Value = .Text
End With

might do what you want.

There are other ways, but if you don't change the cell format to text, or prefix the entry with a single quote ', any subsequent editing of that cell, even inadvertent selection, raises the risk of converting it to the real date.

Upvotes: 2

Related Questions