Leading Zeros based on Len Value Number Formatting

[Pmk1710]

Hey Everyone,

Im stuck with trying to write a code that will keep specific number formatting per LEN value in VBA. Long store short, I am am setting up a workbook that takes Data from "sheet1" - matches to query pulled data in "sheet2" - and populates results in "sheet3" with information from both "sheet1" and "sheet2" based on finding matching data in "A:A" from each sheet.

The issue is formatting the data between the worksheets in order to populate correct matches. I have Item ID codes that range from 1 digit to 14 digits, and they must retain their "text" value.

The Query data is easy with the TRIM function, because the Query data retains the "text" value, just with unnecessary spaces. So I pull my Query and populate in column B, then have the VBA macro for (A:A) = TRIM(B:B), simple

the issue im having is the following:

in "Sheet1" I copy and paste data from suppliers into (A:A) - the Data is primarily UPC and EAN codes with the occasional PLU retail code - so these codes vary in length. AND some of them have leading Zeros while others do not.

I have set up an IF LEN = "" then numberformat = "" VBA for each value between 1 and 13, but I have no idea how to keep the leading zeros where I need them to be.

Here is my code for defining number format per cell LEN:

Sub FormatItemID()
'Item ID Range Definitions
Dim SALEID As Range
Dim r As Range
'Range Values
Set SALEID = Worksheets("SALE DATA").Range(Worksheets("SALE DATA").Range("a2"), Worksheets("SALE DATA").Range("A2").End(xlDown))
'Format Function
For Each r In SALEID
If Len(r.Value) = 1 Then
r.NumberFormat = "0"
End If
If Len(r.Value) = 2 Then
r.NumberFormat = "00"
End If
If Len(r.Value) = 3 Then
r.NumberFormat = "000"
End If
If Len(r.Value) = 4 Then
r.NumberFormat = "0000"
End If
If Len(r.Value) = 5 Then
r.NumberFormat = "00000"
End If
If Len(r.Value) = 6 Then
r.NumberFormat = "000000"
End If
If Len(r.Value) = 7 Then
r.NumberFormat = "0000000"
End If
If Len(r.Value) = 8 Then
r.NumberFormat = "00000000"
End If
If Len(r.Value) = 9 Then
r.NumberFormat = "000000000"
End If
If Len(r.Value) = 10 Then
r.NumberFormat = "0000000000"
End If
If Len(r.Value) = 11 Then
r.NumberFormat = "00000000000"
End If
If Len(r.Value) = 12 Then
r.NumberFormat = "000000000000"
End If
If Len(r.Value) = 13 Then
r.NumberFormat = "0000000000000"
End If
Next
End Sub

What am I missing to retain leading zeros based on (len)r.value, but still retain correct (len).rvalue?

In other words:

How can I keep PLU "1234" as "1234" and not "01234", "001234", "0001234", etc... and at the same time, keep UPC "012345678901" to populate with its leading zero?
And if there is a way to shorten this VBA to begin with, im all up for it!

Peter

 

[jasonb75]

Should everything have a match?

By changing TRIM to VALUE in the column A formula, it goes up to 710 matches.

The reason that I have done it this way is because the sale data has no leading zeros so we have no way of knowing the correct format for each entry. Using VALUE instead of TRIM removes the leading zeros from the ECRS sheet so that both are directly comparable.

This can not distinguish between 2 supposedly different codes that are identical apart from a leading zero. If this is something that can happen then it can not be fixed in excel, or at least not in the workbooks that you have shown here. That would need to be corrected either at the point when the data in 'SUPPLIER U SALES' is generated or where it is sourced from.