I have a macro that uses the following code to copy the value from
the named range "a" to the named range "b" in another workbook:

a.Formula = a.Value2
a.Copy b

The problem is that the "b" cell gets formatted as Date, even though
it's preformatted as Text and the "a" cell also is formatted as Text.
(The "a" cell contains text like 11/2004, so the value is interpreted
as a date on arrival.)

I need to ensure that the "b" cell is formatted as Text, and that it
doesn't convert the original text to a "date number" like 38292. How
can this be done??

Hi

One way:
b.Value = "'" & a.Text

HTH. Best wishes Harald

"Richard H" skrev i melding
9...
I have a macro that uses the following code to copy the value from
the named range "a" to the named range "b" in another workbook:

a.Formula = a.Value2
a.Copy b

The problem is that the "b" cell gets formatted as Date, even though
it's preformatted as Text and the "a" cell also is formatted as Text.
(The "a" cell contains text like 11/2004, so the value is interpreted
as a date on arrival.)

I need to ensure that the "b" cell is formatted as Text, and that it
doesn't convert the original text to a "date number" like 38292. How
can this be done??

Worked beautifully! Takk skal du ha, Harald.

One minor problem, though: The "date" cell is formatted as text in
the original workbook, and written like this:

12/2004

On import, the data is converted to a standard date format, like
this:

Dec.04.

The code you supplied ensures that this is converted to text -
'Dec.04. I would preferably retain 12/2004, as originally entered.
Is that possible??

Richard


"Harald Staff" wrote in
:

Hi

One way:
b.Value = "'" & a.Text

HTH. Best wishes Harald

"Richard H" skrev i melding
9...
I have a macro that uses the following code to copy the value
from the named range "a" to the named range "b" in another
workbook:

a.Formula = a.Value2
a.Copy b

The problem is that the "b" cell gets formatted as Date, even
though it's preformatted as Text and the "a" cell also is
formatted as Text. (The "a" cell contains text like 11/2004, so
the value is interpreted as a date on arrival.)

I need to ensure that the "b" cell is formatted as Text, and
that it doesn't convert the original text to a "date number"
like 38292. How can this be done??

I'm not sure I understood this. When it comes to dates, the original entry
isn't there anymore. Excel converts it to a date immediately. But if mm/yyyy
is what you need, and a contains a real date, then for text
b.Value = "'" & Format$(a.Value, "mm/yyyy")
or, if you want the date to follow
b.Value = a.Value
b.NumberFormat = "mm/yyyy"

A cell can contain a date and display almost whatever. If it's a date then
it shows a real date in the formula bar. If formula bar also says
Dec 04
or
12/2004
then it's text, not a date.

HTH. Best wishes Harald

"Richard H" skrev i melding
1...
Worked beautifully! Takk skal du ha, Harald.

One minor problem, though: The "date" cell is formatted as text in
the original workbook, and written like this:

12/2004

On import, the data is converted to a standard date format, like
this:

Dec.04.

The code you supplied ensures that this is converted to text -
'Dec.04. I would preferably retain 12/2004, as originally entered.
Is that possible??

Richard


"Harald Staff" wrote in
:

Hi

One way:
b.Value = "'" & a.Text

HTH. Best wishes Harald

"Richard H" skrev i melding
9...
I have a macro that uses the following code to copy the value
from the named range "a" to the named range "b" in another
workbook:

a.Formula = a.Value2
a.Copy b

The problem is that the "b" cell gets formatted as Date, even
though it's preformatted as Text and the "a" cell also is
formatted as Text. (The "a" cell contains text like 11/2004, so
the value is interpreted as a date on arrival.)

I need to ensure that the "b" cell is formatted as Text, and
that it doesn't convert the original text to a "date number"
like 38292. How can this be done??

Harald,

you understood, alright. The only puzzling thing now is that the
imported "date" is converted to

'12.2004

even though your code

b.Value = "'" & Format$(a.Value, "mm/yyyy")

definitely tells Excel to return '12/2004.

I can't figure that one out, but I can live comfortably with it.
Thank you!

Regards,
Richard



"Harald Staff" wrote in
:

I'm not sure I understood this. When it comes to dates, the
original entry isn't there anymore. Excel converts it to a date
immediately. But if mm/yyyy is what you need, and a contains a
real date, then for text b.Value = "'" & Format$(a.Value,
"mm/yyyy") or, if you want the date to follow
b.Value = a.Value
b.NumberFormat = "mm/yyyy"

A cell can contain a date and display almost whatever. If it's a
date then it shows a real date in the formula bar. If formula
bar also says Dec 04
or
12/2004
then it's text, not a date.

HTH. Best wishes Harald

"Richard H" skrev i melding
1...
Worked beautifully! Takk skal du ha, Harald.

One minor problem, though: The "date" cell is formatted as text
in the original workbook, and written like this:

12/2004

On import, the data is converted to a standard date format,
like this:

Dec.04.

The code you supplied ensures that this is converted to text -
'Dec.04. I would preferably retain 12/2004, as originally
entered. Is that possible??

Richard


"Harald Staff" wrote in
:

Hi

One way:
b.Value = "'" & a.Text

HTH. Best wishes Harald

"Richard H" skrev i melding
9...
I have a macro that uses the following code to copy the
value from the named range "a" to the named range "b" in
another workbook:

a.Formula = a.Value2
a.Copy b

The problem is that the "b" cell gets formatted as Date,
even though it's preformatted as Text and the "a" cell also
is formatted as Text. (The "a" cell contains text like
11/2004, so the value is interpreted as a date on arrival.)

I need to ensure that the "b" cell is formatted as Text, and
that it doesn't convert the original text to a "date number"
like 38292. How can this be done??