String converted to date

[Guest]

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

 

[Guest]

How about:

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

HTH
Jason
Atlanta, GA

 

[Guest]

Thanks all.

 

[William Benson]

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

 

[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

 

[Ron Rosenfeld]

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]

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