String converted to date

[Tony]

I have the string '1/1/05 in cell A1. When I execute the following code:

Range("A2").Value = Range("A1").Value

VBA converts the string to a date (i.e., number), which is not what I want.

I've tried

Range("A2").Value = Cstr(Range("A1").Value)

and even passing the value through a string variable, but the result is the
same. If the string is _1/1/05, then there is no problem. Ideally, I'd like
the contents of A1 to be a date and have it converted to a string when placed
in A2, but that seems too much to ask if I can't even copy a string from A1
to A2. Help appreciated.

Using Excel 97

Tony

[Trevor Shuttleworth]

Tony

try:

Range("A2").Text = Range("A1").Text

Regards

Trevor


"Tony" <[email protected]> wrote in message
news:[email protected]...
>I have the string '1/1/05 in cell A1. When I execute the following code:
>
> Range("A2").Value = Range("A1").Value
>
> VBA converts the string to a date (i.e., number), which is not what I
> want.
>
> I've tried
>
> Range("A2").Value = Cstr(Range("A1").Value)
>
> and even passing the value through a string variable, but the result is
> the
> same. If the string is _1/1/05, then there is no problem. Ideally, I'd
> like
> the contents of A1 to be a date and have it converted to a string when
> placed
> in A2, but that seems too much to ask if I can't even copy a string from
> A1
> to A2. Help appreciated.
>
> Using Excel 97
>
> Tony
>
>

[Jason Morin]

How about:

Range("A2").Value = Chr(39) & Range("A1").Value

HTH
Jason
Atlanta, GA

"Tony" wrote:

> I have the string '1/1/05 in cell A1. When I execute the following code:
>
> Range("A2").Value = Range("A1").Value
>
> VBA converts the string to a date (i.e., number), which is not what I want.
>
> I've tried
>
> Range("A2").Value = Cstr(Range("A1").Value)
>
> and even passing the value through a string variable, but the result is the
> same. If the string is _1/1/05, then there is no problem. Ideally, I'd like
> the contents of A1 to be a date and have it converted to a string when placed
> in A2, but that seems too much to ask if I can't even copy a string from A1
> to A2. Help appreciated.
>
> Using Excel 97
>
> Tony
>
>

[Tony]

Thanks all.

"Tony" wrote:

> I have the string '1/1/05 in cell A1. When I execute the following code:
>
> Range("A2").Value = Range("A1").Value
>
> VBA converts the string to a date (i.e., number), which is not what I want.
>
> I've tried
>
> Range("A2").Value = Cstr(Range("A1").Value)
>
> and even passing the value through a string variable, but the result is the
> same. If the string is _1/1/05, then there is no problem. Ideally, I'd like
> the contents of A1 to be a date and have it converted to a string when placed
> in A2, but that seems too much to ask if I can't even copy a string from A1
> to A2. Help appreciated.
>
> Using Excel 97
>
> Tony
>
>

[William Benson]

I get a runtime error with the below: "Object Required". Am using Excel
2000.

"Trevor Shuttleworth" <[email protected]> wrote in message
news:[email protected]...
> Tony
>
> try:
>
> Range("A2").Text = Range("A1").Text
>
> Regards
>
> Trevor
>
>
> "Tony" <[email protected]> wrote in message
> news:[email protected]...
> >I have the string '1/1/05 in cell A1. When I execute the following code:
> >
> > Range("A2").Value = Range("A1").Value
> >
> > VBA converts the string to a date (i.e., number), which is not what I
> > want.
> >
> > I've tried
> >
> > Range("A2").Value = Cstr(Range("A1").Value)
> >
> > and even passing the value through a string variable, but the result is
> > the
> > same. If the string is _1/1/05, then there is no problem. Ideally, I'd
> > like
> > the contents of A1 to be a date and have it converted to a string when
> > placed
> > in A2, but that seems too much to ask if I can't even copy a string from
> > A1
> > to A2. Help appreciated.
> >
> > Using Excel 97
> >
> > Tony
> >
> >
>
>

[Chip Pearson]

The Text property is read-only, so you have to use the Value
property. E.g.,

Range("A2").Value = Range("A1").Text

Admittedly, the error message is less than clear.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"William Benson" <[email protected]> wrote in message
news:%[email protected]...
>I get a runtime error with the below: "Object Required". Am
>using Excel
> 2000.
>
> "Trevor Shuttleworth" <[email protected]> wrote in
> message
> news:[email protected]...
>> Tony
>>
>> try:
>>
>> Range("A2").Text = Range("A1").Text
>>
>> Regards
>>
>> Trevor
>>
>>
>> "Tony" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have the string '1/1/05 in cell A1. When I execute the
>> >following code:
>> >
>> > Range("A2").Value = Range("A1").Value
>> >
>> > VBA converts the string to a date (i.e., number), which is
>> > not what I
>> > want.
>> >
>> > I've tried
>> >
>> > Range("A2").Value = Cstr(Range("A1").Value)
>> >
>> > and even passing the value through a string variable, but
>> > the result is
>> > the
>> > same. If the string is _1/1/05, then there is no problem.
>> > Ideally, I'd
>> > like
>> > the contents of A1 to be a date and have it converted to a
>> > string when
>> > placed
>> > in A2, but that seems too much to ask if I can't even copy a
>> > string from
>> > A1
>> > to A2. Help appreciated.
>> >
>> > Using Excel 97
>> >
>> > Tony
>> >
>> >
>>
>>
>
>

[Ron Rosenfeld]

On Fri, 10 Jun 2005 18:42:24 -0500, "Chip Pearson" <[email protected]> wrote:

>The Text property is read-only, so you have to use the Value
>property. E.g.,
>
>Range("A2").Value = Range("A1").Text
>
>Admittedly, the error message is less than clear.

I cannot get that to work if A2 is formatted as General.

If I format it as text, then that works, as does this:

===============
Sub foo()
With Range("A2")
.NumberFormat = "@"
.Value = Range("A1").Value
End With
End Sub
==============




--ron

[Ron Rosenfeld]

On Fri, 10 Jun 2005 20:39:42 -0400, Ron Rosenfeld <[email protected]>
wrote:

>On Fri, 10 Jun 2005 18:42:24 -0500, "Chip Pearson" <[email protected]> wrote:
>
>>The Text property is read-only, so you have to use the Value
>>property. E.g.,
>>
>>Range("A2").Value = Range("A1").Text
>>
>>Admittedly, the error message is less than clear.
>
>I cannot get that to work if A2 is formatted as General.
>
>If I format it as text, then that works, as does this:
>
>===============
>Sub foo()
> With Range("A2")
> .NumberFormat = "@"
> .Value = Range("A1").Value
> End With
>End Sub
>==============
>
>
>
>
>--ron

By "not work" I mean that the value stored in A2 ([a1].text) gets converted to
a date, if A2 is formatted as General.


--ron