Store Number as Text

[jfish1288]

I'm using an excel file to generate a list of UPC codes to send to a printer.
The UPC code needs to be 12 digits.
In the source excel file the UPC codes are stored as text with a leading zero.
Using the code below, they are populating the new list as numbers without the leading zero.
Formatting as a 12 digit number does not work because the printer does not read the leading zero.
It needs to be stored as text.
What am I doing wrong?

Source sheet examples (number is stored as text with the little green corner in the cell and the warning sign)

ColumnA	ColumnB	ColumnC	ColumnD	ColumnE	UPC
A2	B2	C2	D3	E4	097963547574
A3	B3	C3	D3	E3	097963664127

<tbody>
</tbody>

Current Output

ColumnA	ColumnB	UPC
A2	B2	97963547574
A3	B3	97963664127

<tbody>
</tbody>

Dim UPC_code As String

UPC_code = sourceWS.Cells(iRow, 6).Text
expandWS.Cells(printRow, 3).Value = UPC_code

 

[Joe4]

The ".Value" is coercing your text value back to numeric. Get rid of it here:

expandWS.Cells(printRow, 3)[COLOR=#ff0000].Value[/COLOR] = UPC_code

like this:

expandWS.Cells(printRow, 3) = UPC_code

 

[jfish1288]

I still have the same issue

 

[Modesto73]

If the cell is formatted as number or general, it will interpret a number as a number, whether that number is stored in VBA variables as a string is irrelevant. I would suggest trying to format the cell/range as text fist:

Dim UPC_code As String


UPC_code = sourceWS.Cells(iRow, 6).Text
expandWS.Cells(printRow, 3).NumberFormat = "@"
expandWS.Cells(printRow, 3).Value = UPC_code

 

[jfish1288]

If the cell is formatted as number or general, it will interpret a number as a number, whether that number is stored in VBA variables as a string is irrelevant. I would suggest trying to format the cell/range as text fist:

Dim UPC_code As String


UPC_code = sourceWS.Cells(iRow, 6).Text
expandWS.Cells(printRow, 3).NumberFormat = "@"
expandWS.Cells(printRow, 3).Value = UPC_code

That did it. Thanks!

 

[Joe4]

I still have the same issue

Odd. It worked for me when I tested it out.
My next suggestion was going to be to pre-format the cell/column/row as Text, but looks like Modesto73 already got you there.