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

[staying]

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' , other tha
PasteSpecial method?

Thanks a lot

 

[Guest]

Format the destination cell as text prior to running the macro.

 

[Guest]

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

 

[staying]

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

 

[Guest]

I can't duplicate the problem. Please post an example.

 

[staying]

Gary''s Student, please find the attachment.


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