VBA Excel - Keep Leading Zeros if they exist

[hellfire45]

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!

 

[kweaver]

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]

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.

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]

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]

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")