'
' UniversalProductCode Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
ActiveCell.FormulaR1C1 = "0"
Range("B1").Select
Selection.End(xlDown).Select
Range("A1000").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Range("C1:C1000").Activate
Selection.FormulaR1C1 = "=RC[-2]&RC[-1]"
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("B1:B1000").Activate
Selection.FormulaR1C1 = "=LEN(RC[-1])"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C1:C1000").Activate
Selection.FormulaR1C1 = _
"=IF(RC[-1]=10, ""1"", IF(RC[-1]= 9, ""11"", IF(RC[-1]= 8, ""111"", IF(RC[-1]= 7, ""1111"", IF(RC[-1]= 6, ""11111"", IF(RC[-1]= 5, ""111111"", IF(RC[-1]= 4, ""1111111"", ""Thenga"")))))))"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("C1:C1000").Activate
Selection.FormulaR1C1 = "=RC[-2]&RC[-1]"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
OtherChar:="-", FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(2, 1), Array(3, 1 _
), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1) _
), TrailingMinusNumbers:=True
Range("M1:M1000").Activate
Selection.FormulaR1C1 = _
"=((RC[-11]+RC[-9]+RC[-7]+RC[-5]+RC[-3]+RC[-1])*3)+RC[-10]+RC[-8]+RC[-6]+RC[-4]+RC[-2]"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("C1:C1000").Activate
Selection.FormulaR1C1 = "=RIGHT(RC[-1],1)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("C1:C1000").Activate
Selection.FormulaR1C1 = "=-(RC[-1]-10)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Selection.Replace What:="10", Replacement:="0", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("C1:C1000").Activate
Selection.FormulaR1C1 = "=RC[-2]&RC[-1]"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Selection.Replace What:="#Value", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="#Value!", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub