Stigmata101
New Member
- Joined
- Feb 27, 2014
- Messages
- 25
Hey guys
I am trying to copy all the unique values from a column on one sheet to a column on another sheet. I am using the below which works, however, when inserting the value on the second sheet, it keeps changing the format.
The problem I am having is that the source data is in TEXT format, but when it inserts the values it changes the formant and I need it to stay in TEXT format. I cannot have it changing to scientific, some of the values end in E7 which is then converted into a scientific format, for example, 123E7 becomes 1230000000. Also, other values are converted to a number format.
How do I complete the unique copy and maintain the original data's format, whatever it may be?
Appreciate any assitance.
Stig
I am trying to copy all the unique values from a column on one sheet to a column on another sheet. I am using the below which works, however, when inserting the value on the second sheet, it keeps changing the format.
VBA Code:
Sub Fetch_Consumed_GPNs()
Dim rLastCell As Range
Dim cell As Range, i As Long
Dim cOrders As Collection
Set cOrders = New Collection
Dim y As Worksheet
With ActiveWorkbook.Worksheets("Consumed")
'Find last used cell
Set rLastCell = .Range("B30000").End(xlUp)
On Error Resume Next
For Each cell In .Range("B1:B" & rLastCell.Row)
cOrders.Add cell.Value, CStr(cell.Value)
Next cell
On Error GoTo 0
End With
With ActiveWorkbook.Worksheets("Weekly Summary")
For i = 2 To cOrders.Count
.Range("A" & i).Value = cOrders(i)
Next i
End With
End Sub
The problem I am having is that the source data is in TEXT format, but when it inserts the values it changes the formant and I need it to stay in TEXT format. I cannot have it changing to scientific, some of the values end in E7 which is then converted into a scientific format, for example, 123E7 becomes 1230000000. Also, other values are converted to a number format.
How do I complete the unique copy and maintain the original data's format, whatever it may be?
Appreciate any assitance.
Stig