1

I have a small problem with a date copy/paste in VBA. I want to get dates which appear in dd/mm/yyyy format into text. My Excel has original settings in English.

I tried:

Selection.NumberFormat = "@"

but it returns text in mm/dd/yyyy format. Would anyone know how to keep the dd/mm/yyyy format? I tried to change the "Locale" parameters of my range but it does not work.

Many thanks, Vincent

VincentH
  • 1,009
  • 4
  • 13
  • 24

2 Answers2

0

I just saw a similar question....You may format your date with,

Format(urdate_intext, "dd/mm/yyyy")

Excel does this based on the regional settings in your computer. If you wish to change regional settings pleae take a look at this: Excel Date Format changed after running code.

And hey BTW, you don't have to select a range to work with it. :) Set the range to a range object.

Community
  • 1
  • 1
bonCodigo
  • 14,268
  • 1
  • 48
  • 91
  • thanks for the idea. I don't why but it only worked on some of my dates. I parsed instead. – VincentH Jan 09 '13 at 15:59
  • Sorry. With some of my dates format(date,"dd/mm/yyyy") did not work correctly. I don't really know why. Therefore I parsed my dates into arrays and concatenate...It is not nice but it works – VincentH Jan 09 '13 at 17:43
0

There's a property in the Range object named 'Text'. It gets what is visible in the spreadsheet. Then you can store it in a string variable and paste it in a previously formatted-as-text cell (could be the same one in a loop). So you can:

Dim tt as string
tt = ActiveCell.Text
Selection.NumberFormat = "@"
Selection.Value = tt

The other solution you had probably only worked in the dates where the day was <13, because it took the day as month.

Sama Tori
  • 121
  • 1
  • 5