I don't think you'd need this at the end:

For Each rcell In destRng
rcell.Value = CStr(rcell.Value)
Next



Norman Jones wrote:

Hi Deluth,

And if the ranges were multi-area ranges, perhaps something like:

Sub Test3()
Dim destRng As Range
Dim srcRng As Range
Dim rcell As Range
Dim i As Long

Set srcRng = Range("C1:C3,C5:C7,C10:C11")
Set destRng = Range("A1:A3,B5:B7,A10:A11")

destRng.NumberFormat = "@"

For i = 1 To srcRng.Areas.Count
destRng.Areas(i).Value = srcRng.Areas(i).Value
Next i

For Each rcell In destRng
rcell.Value = CStr(rcell.Value)
Next
End Sub

---
Regards,
Norman

"Norman Jones" wrote in message
...
Hi Deluth,

For example, a value of "2200505000099" became "2.20051E+12"

This appears only to happen with 12+ digit numbers.

For single cell ranges, the following worked for me:

Sub Test1()
Dim destCell As Range, srcCell As Range

Set destCell = Range("A1")
Set srcCell = Range("C1")

srcCell.Value = "2200505000099" '13 digit Test value

With destCell
.NumberFormat = "@"
.Value = CStr(srcCell.Value)
End With
End Sub

For multi-cell ranges, the following worked for me:

Sub Test2()
Dim destRng As Range
Dim srcRng As Range
Dim rcell As Range

Set srcRng = Range("C1:C10")
Set destRng = Range("A1:A10")

destRng.NumberFormat = "@"
destRng.Value = srcRng.Value
For Each rcell In destRng
rcell.Value = CStr(rcell.Value)
Next
End Sub

---
Regards,
Norman

--

Dave Peterson