1

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?

Community
  • 1
  • 1
m.swiss
  • 104
  • 7

3 Answers3

2

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.

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
2

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)
Jochen
  • 1,254
  • 1
  • 7
  • 9
0

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.

Community
  • 1
  • 1
Mátray Márk
  • 455
  • 5
  • 17
  • `xlPasteAll` will copy the formula also; whereas the code in the OP removes the formula. Try pasting just the values. – Ron Rosenfeld Jul 01 '16 at 11:21
  • Sorry I misunderstood the question. In that case you could us xlPasteFormats. Edited my original commen. – Mátray Márk Jul 01 '16 at 12:17
  • It may have been me who misunderstood, or the OP may not have explained clearly what he wanted to do. But don't forget to also copy over the value; and test to see what you get when you use your method. – Ron Rosenfeld Jul 01 '16 at 12:23
  • With `Selection.Value = Selection.Value` you are pasting the values in the same cells where the formula was. Therefore, the formatting is the same and, furthermore, you can by-pass the clipboard with this method – m.swiss Jul 01 '16 at 13:29