Need to set Column format as text
Dave and Norman,
Awesome!! Both methods worked!! You rock!!
Thanks so much!
Deluth
"Dave Peterson" wrote:
Ah, I should have read the whole thread <vbg.
But depending on the format of the source range, this could work, too:
For i = 1 To srcRng.Areas.Count
destRng.Areas(i).Value = srcRng.Areas(i).Text
Next i
Norman Jones wrote:
Hi Dave,
I don't think you'd need this at the end:
For Each rcell In destRng
rcell.Value = CStr(rcell.Value)
Next
If the srcRng included 12+ digit numbers, failure to include this
For...Next clause results in such numbers appearing in the destRng in
scientific notation representation.
This,at least, was my experience testing under xl2k; I have not, as yet,
tested with other versions
---
Regards,
Norman
"Dave Peterson" wrote in message
...
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
--
Dave Peterson
|