Thread: 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 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' , other than PasteSpecial method?

Thanks a lot.

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' , 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
>
>

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' , 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
>
>

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?

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
>
>