I'm attempting to automate a procedure that requires pressing the "Repair UPC" button on an Excel Spreadsheet at the moment. If a text-formatted string is 12 characters or longer, this button will erase the last digit and change the number format to "0-00000-00000." The vast majority of UPCs this sheet will deal with adhere to the UPC-A standard, but like many systems, ours ignores the last check digit; as a result, it must be trimmed to 11 for a VLOOKUP to function correctly. Some must be left intact since they adhere to the 13-digit EAN standard.
Right now I have the following function located in a module named Helpers:
Public Function deleteCheckDigit(theUPC) As String
If Len(theUPC) = 12 Then
deleteCheckDigit = Left(theUPC, 11)
deleteCheckDigit = theUPC
The Private Sub on the Worksheet is:
Private Sub Worksheet_Change(ByVal Target As Range)
theCol = Helpers.GetColumnFromAddress(Target.Address)
theValue = Range(Target.Address).Value
If theCol = "A" Then
If Len(theValue) = 12 Then
Target.Value = Helpers.deleteCheckDigit(theValue)
Range(Target.Address).NumberFormat = "0-00000-00000"
When I enter in a UPC and hit Enter, Excel throws a Compile Error stating ByRef argument type mismatch. I tested VarType(theValue) and it returns 8 meaning it is a string, so I am not seeing what is causing this error.
Note: I have this set up with an If/Then block to check the column letter because there is some other modifications being done to different columns.