Copying a Range changes value (not good)

[Dave]

The following has a user enter a named range in an
InputBox
The range is then copied to EG3 on sheet A
It all works fine except: The contents of the range are
mostly numbers that are preceded with a single quote and
a zero. e.g. '020140
When the contents are copied over, it comes over as a
number 20140 I need it with the preceding '0

??
Thanks for any help given.

Dave

Here is the snippet of code:


Range("b6").Select
cv = ActiveCell.Value

Dim rng As Range
Dim strRangeName As String
strRangeName = InputBox("Enter Construction
Name", "Construction", cv, 1, 1)

Set rng = Worksheets("CONSTABL").Names
(strRangeName).RefersToRange

Dim cell As Range
Dim rngTarget As Range
Set rngTarget = Worksheets("A").Range("EG3")

Dim i As Integer

For Each cell In rng
rngTarget.Offset(i, 0).Value = cell.Value
i = i + 1
Next

 

[Frank Kabel]

Hi
try changing the lines
For Each cell In rng
rngTarget.Offset(i, 0).Value = cell.Value
i = i + 1
Next

to
For Each cell In rng
rngTarget.Offset(i, 0).Numberformat = "@"
rngTarget.Offset(i, 0).Value = cell.Value
i = i + 1
Next

 

[Stevie_mac]

Try...

For Each cell In rng
If TypeName(cell.Value) = "String" _
And IsNumeric(cell.Value) Then
rngTarget.Offset(i, 0).Value = "'" & cell.Text
Else
rngTarget.Offset(i, 0).Value = cell.Text
End If
rngTarget.Offset(i, 0).NumberFormat = cell.NumberFormat
i = i + 1
Next

 

[Dave]

Steve and Frank,

That worked great, Thank You!

This community is great!

Dave