VBA Excel - Keep Leading Zeros if they exist

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
454
Hey guys,

So I am transposing data into a file and some of the rows have part numbers with leading zeros and some do not. In the event that they have leading zeros, the VBA is erasing them when transposing. I would just format all of the numbers as text but some of them are not text.

So in the instance that the part number is formatted as text, I want to transpose it that way with the leading zeros. In the event that the part number is normal we'll say with either letters or numbers but no leading zeros, I just want to transpose it as is.

Any suggestions on maintaining these leading zeros while not modifying the other part numbers? Thanks!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,118
Office Version
  1. 365
  2. 2010
If you do a custom format such as: 00000;;@ you'll have 5-digit numbers w/leading 0s and retain any of the text values.
 

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
454
I'm using a VBA loop to transpose data in one spreadsheet, which is formatted like a nightmare, into a cleaner, different spreadsheet. Something like below but it's not working. As you can see, I am trying to find the solution lol.

VBA Code:
For x_Row = 2 To mid_last_row
                        Application.StatusBar = "Alternate " & x_Row & " of " & mid_last_row & " processed."
                        current_Sku = Mid.Cells(x_Row, mid_part_num).Value
                        last_Sku_Row = .Columns(mtm_col).Find("*", , , , xlRows, xlPrevious).Row
                                        
                        Set search_range = .Range(.Cells(2, mtm_col), .Cells(last_Sku_Row, mtm_col))
                        Set sku_exist = search_range.Find(current_Sku, LookIn:=xlValues)
                        If Not sku_exist Is Nothing Then
                        Else
                                    last_y_col = Mid.Cells(x_Row, Mid.Columns.Count).End(xlToLeft).Column
                                    For y_Row = mid_alt_num To last_y_col
                                                If Left(current_Sku, 1) = "0" Then
                                                            .Cells(last_Sku_Row + 1, 1).Value = Trim(Mid.Cells(x_Row, mid_part_num))
                                                            .Cells(last_Sku_Row + 1, 2).Value = CStr(Trim(Mid.Cells(x_Row, mid_PArt_desc)))
                                                            .Cells(last_Sku_Row + 1, 3).Value = CStr(Trim(Mid.Cells(x_Row, y_Row)))
                                                Else
                                                            .Cells(last_Sku_Row + 1, 1).Value = Trim(Mid.Cells(x_Row, mid_part_num))
                                                            .Cells(last_Sku_Row + 1, 2).Value = Trim(Mid.Cells(x_Row, mid_PArt_desc))
                                                            .Cells(last_Sku_Row + 1, 3).Value = Trim(Mid.Cells(x_Row, y_Row))
                                                End If
                                                last_Sku_Row = last_Sku_Row + 1
                                    Next
                        End If
            Next
 

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
454
This is still not working. Can anybody help? I'm just transposing numbers from one worksheet to another but this one column has numbers stored as text with leading zeroes and it has numbers stored as numbers. Basically I need to transpose the figures onto the second worksheet exactly as they appear in the first worksheet. With leading zeros when they exist and without when they don't. Any ideas? I cannot see the find the right "copy paste" or x.value = y sort of method that will maintain the zeroes.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,481
Office Version
  1. 365
Platform
  1. Windows
What happens if you remove the ".Value" part from all your Cell assignments?

Out of curiosioty, are all the entries 5 characters long?
If so, and the suggestion above does not work, you can use the FORMAT function in Excel, i.e.
FORMAT(some number, "00000")
 

Forum statistics

Threads
1,144,162
Messages
5,722,845
Members
422,460
Latest member
VBA_Noob01

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