Collection data keeps changing Cell format

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.

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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,479
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Try changing the '.Value' in your last section of code to '.Text'

VBA Code:
.Range("A" & i).Text = cOrders(i)
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,479
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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:

VBA Code:
    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
 
Solution

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
396
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@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.
 

Stigmata101

New Member
Joined
Feb 27, 2014
Messages
25
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

Active Member
Joined
Aug 8, 2021
Messages
396
Office Version
  1. 365
Platform
  1. Windows
johnnyL deserves the answer here, as cell formatting was in the code he provided. :)
 

Forum statistics

Threads
1,144,342
Messages
5,723,813
Members
422,518
Latest member
quack_quack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top