Number stored as text. How do I keep the number format?
 

Hi, all.

I came across a problem when trying to put a value from one cell t
another.
Here goes a sample code.

=======================
Sub aaa()
Dim rng_src as range

Set rng_src = activecell
activecell.offset(1,0).value = rng_src.value
activecell.offset(1,0).NumberFormat = rng_src.NumberFormat

End Sub
=======================

It works fine with regular numeric or string values, but gets ver
frustrating when the source cell is "number stored as text", whethe
the cell format was overriden with an apostrophe or it was value-copie
from such ranges.

I can't find anything in the vba help file or the object browser.

Is there any way to work around this pain in the 's':mad: , other tha
PasteSpecial method?

Thanks a lot

--
stayin
-----------------------------------------------------------------------
staying's Profile: http://www.excelforum.com/member.php...fo&userid=2323
View this thread: http://www.excelforum.com/showthread.php?threadid=55088

Number stored as text. How do I keep the number format?
 

Format the destination cell as text prior to running the macro.
--
Gary's Student


"staying" wrote:


Hi, all.

I came across a problem when trying to put a value from one cell to
another.
Here goes a sample code.

=======================
Sub aaa()
Dim rng_src as range

Set rng_src = activecell
activecell.offset(1,0).value = rng_src.value
activecell.offset(1,0).NumberFormat = rng_src.NumberFormat

End Sub
=======================

It works fine with regular numeric or string values, but gets very
frustrating when the source cell is "number stored as text", whether
the cell format was overriden with an apostrophe or it was value-copied
from such ranges.

I can't find anything in the vba help file or the object browser.

Is there any way to work around this pain in the 's':mad: , other than
PasteSpecial method?

Thanks a lot.


--
staying
------------------------------------------------------------------------
staying's Profile: http://www.excelforum.com/member.php...o&userid=23230
View this thread: http://www.excelforum.com/showthread...hreadid=550888

Number stored as text. How do I keep the number format?
 

I forgot the a single quote ( apostrophe) is a special case:

Sub bbb()
Dim rng_src As Range

Set rng_src = ActiveCell
ActiveCell.Offset(1, 0).NumberFormat = rng_src.NumberFormat
If rng_src.PrefixCharacter = "'" Then
ActiveCell.Offset(1, 0).Value = Chr(39) & rng_src.Value
Else
ActiveCell.Offset(1, 0).Value = rng_src.Value
End If
End Sub

seems to work in all cases

--
Gary''s Student


"staying" wrote:


Hi, all.

I came across a problem when trying to put a value from one cell to
another.
Here goes a sample code.

=======================
Sub aaa()
Dim rng_src as range

Set rng_src = activecell
activecell.offset(1,0).value = rng_src.value
activecell.offset(1,0).NumberFormat = rng_src.NumberFormat

End Sub
=======================

It works fine with regular numeric or string values, but gets very
frustrating when the source cell is "number stored as text", whether
the cell format was overriden with an apostrophe or it was value-copied
from such ranges.

I can't find anything in the vba help file or the object browser.

Is there any way to work around this pain in the 's':mad: , other than
PasteSpecial method?

Thanks a lot.


--
staying
------------------------------------------------------------------------
staying's Profile: http://www.excelforum.com/member.php...o&userid=23230
View this thread: http://www.excelforum.com/showthread...hreadid=550888

Number stored as text. How do I keep the number format?
 

Thanks, Gary''s Student.

Your solution works well except for one case. The problem persists whe
you copy a cell which was text formatted with an apostrophe and valu
paste it into another cell.

Is there a hidden property in those cells

--
stayin
-----------------------------------------------------------------------
staying's Profile: http://www.excelforum.com/member.php...fo&userid=2323
View this thread: http://www.excelforum.com/showthread.php?threadid=55088

Number stored as text. How do I keep the number format?
 

I can't duplicate the problem. Please post an example.
--
Gary's Student


"staying" wrote:


Thanks, Gary''s Student.

Your solution works well except for one case. The problem persists when
you copy a cell which was text formatted with an apostrophe and value
paste it into another cell.

Is there a hidden property in those cells?


--
staying
------------------------------------------------------------------------
staying's Profile: http://www.excelforum.com/member.php...o&userid=23230
View this thread: http://www.excelforum.com/showthread...hreadid=550888

Number stored as text. How do I keep the number format?
 

Gary''s Student, please find the attachment.


+-------------------------------------------------------------------+
|Filename: Number stored as text.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4883 |
+-------------------------------------------------------------------+

--
staying


------------------------------------------------------------------------
staying's Profile: http://www.excelforum.com/member.php...o&userid=23230
View this thread: http://www.excelforum.com/showthread...hreadid=550888