Collection data keeps changing Cell format

[Stigmata101]

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.

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

 

[johnnyL]

Try changing the '.Value' in your last section of code to '.Text'

.Range("A" & i).Text = cOrders(i)

 

[Gokhan Aycan]

.Value2 might be better.

TEXT vs VALUE vs VALUE2 – Slow TEXT and how to avoid it

 

[Stigmata101]

Try changing the '.Value' in your last section of code to '.Text'

.Range("A" & i).Text = cOrders(i)

This gives me a run-time error.

 

[Stigmata101]

.Value2 might be better.

TEXT vs VALUE vs VALUE2 – Slow TEXT and how to avoid it

.Value2 works but still changes the number formatting.

 

[johnnyL]

This gives me a run-time error.

Ok, instead of doing the text thing I suggested, How about inserting a line before your last loop section:

    With ActiveWorkbook.Worksheets("Weekly Summary")
        Columns(1).NumberFormat = "@"
'
        For i = 2 To cOrders.Count
            .Range("A" & i).Value = cOrders(i)
        Next i
    End With
End Sub

 

[Gokhan Aycan]

@Stigmata101 .Text is read-only.

Just to make sure, did you add the values to the collection with .Value2?

Anyway,

Columns(1).NumberFormat = "@" as proposed above should fix it I suppose.

 

[johnnyL]

@Stigmata101 .Text is read-only.

Just to make sure, did you add the values to the collection with .Value2?

That works also!

 

[Stigmata101]

Hey Gokhan

I did not need to use the value2 during collection.

"Columns(1).NumberFormat = "@" as proposed above should fix it I suppose." this worked perfectly for what I was trying to do.

 

[Gokhan Aycan]

johnnyL deserves the answer here, as cell formatting was in the code he provided.