Saturday, August 10, 2013

Convert range formatted as date to text

Convert range formatted as date to text

I have range of cells in date format, formatted as dd.mm.yyyy, like this:
05.10.1993
05.10.1993
05.10.1993
05.10.1993
and I want to convert this range of cells to text format, using VBA, but
without iterating each cell in range (as it is slow for large range).
I used this code:
Set rSel = Selection
aDate = rSel.Value
rSel.NumberFormat = "@"
rSel.Value = aDate
So I assign selected range to intermediate array, then convert the range
to text format and assign the array back to selected range.
Result is this text:
5/10/1993
5/10/1993
5/10/1993
5/10/1993
and I wonder where did format conversion took place, as if I debug.print
for example aDate(1,1) I get expected 05.10.1993 value? Or how can I
instruct format in simple snippet I posted so that I get expected text as
a result?

No comments:

Post a Comment