create new products numbers in excel

[eli_h]

Hi,

I would like to create a new products numbers column in Excel.

I have my supplier "SKU's numbers" in column A , I would like to create my "SKU's numbers" in XXXX format (only numbers) in column B.

Could someone help me pls todo it with Macro or if it possible todo it in formula ?

The code should check in the excel sheet, that it is not generate a new SKU that was used before !

Any help please? Thanks a lot,

Eli

 

[Andrew Poulsom]

Please give some examples of the entries in column A and how they should appear in column B.

 

[eli_h]

Please give some examples of the entries in column A and how they should appear in column B.

Hi,

Thank you very much for your reply !!

here is example of column A and how should the code will create SKU in column B

W1066UA1GM 1124
W1066UA1GE 3455
W1066UA1GS 7655
W1066UA1GQ 3699
W1066UB1GE 8933
W1066UB2GM 4452
W1066UB2GS 4116
W1066UB2GE 5622
W1066UB2GQ 7900

Note please that the SKU numbers is column B should be random numbers, But the code should check when it generates new random SKU in column B, that this SKU was not used before.

like that, if I enter a new supplier SKU like in the folowing example:

W1066UA1GM 1124
W1066UA1GE 3455
W1066UA1GS 7655
W1066UA1GQ 3699
W1066UB1GE 8933
W1066UB2GM 4452
W1066UB2GS 4116
W1066UB2GE 5622
W1066UB2GQ 7900
D3-2G1066X
D3-1G1066X
D3-1G1066A
D31066S1GA
D31066S4GH
D31066S2GH

The code should add a new SKU numbers in Column B, but will check that the SKU numbers it insert were not used !

pls if you need more details I am here to answer. )
Thanks a lot again!
Eli

 

[Andrew Poulsom]

Try this in the module for the Worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    Dim Test As Integer
    If Target.Column <> 1 Then Exit Sub
    Application.EnableEvents = False
    For Each Cell In Target
        With Cell.Offset(0, 1)
            If IsEmpty(Cell) Then
                .ClearContents
            Else
                .NumberFormat = "0000"
                Do
                    .Value = Int(9999 * Rnd())
                    Test = WorksheetFunction.CountIf(.EntireColumn, .Value)
                Loop Until Test <= 1
            End If
        End With
    Next Cell
    Application.EnableEvents = True
End Sub

 

[eli_h]

Try this in the module for the Worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    Dim Test As Integer
    If Target.Column <> 1 Then Exit Sub
    Application.EnableEvents = False
    For Each Cell In Target
        With Cell.Offset(0, 1)
            If IsEmpty(Cell) Then
                .ClearContents
            Else
                .NumberFormat = "0000"
                Do
                    .Value = Int(9999 * Rnd())
                    Test = WorksheetFunction.CountIf(.EntireColumn, .Value)
                Loop Until Test <= 1
            End If
        End With
    Next Cell
    Application.EnableEvents = True
End Sub

Thank you very much for the code Mr. Andrew Poulsom!

I copy it to module section, but when I tried to run the macro "Worksheet_Change" it gives me a message "argument not optional" ..

My column A in the input excel file was this: (there was not column B)

W1066UA1GM
W1066UA1GE
W1066UA1GS
W1066UA1GQ
W1066UB1GE
W1066UB2GM
W1066UB2GS
W1066UB2GE
W1066UB2GQ
D3-2G1066X
D3-1G1066X
D3-1G1066A
D31066S1GA
D31066S4GH
D31066S2GH
D3-4G1066S
D3-2G1066S
D3-1G1066S
W12G1066KI
W6G1066K
W4G1066K



Any advise please?

Thanks,
Eli

 

[Andrew Poulsom]

The code goes in the module for the worksheet containing your data (not in a General module). You don't need to run the macro. It will run automatically when an entry in column A changes.

If you already have entries in column A select them and choose Edit|Copy then Edit|Paste to put numbers column B.

 

[eli_h]

The code goes in the module for the worksheet containing your data (not in a General module). You don't need to run the macro. It will run automatically when an entry in column A changes.

If you already have entries in column A select them and choose Edit|Copy then Edit|Paste to put numbers column B.

Hi Mr Andrew,

look pls what I did..

I opened a new worksheet, then in menu, select tools then Macro then microsoft Visual basic then copied your code to the main window. saved the file.
went back to worksheet copy all column A and even delete it, then copy back the entries and nothing changed.. SKU's numbers on column B were not created..

Do I did something wrong?

thanks,
Eli

 

[eli_h]

Hi Mr Andrew,

look pls what I did..

I opened a new worksheet, then in menu, select tools then Macro then microsoft Visual basic then copied your code to the main window. saved the file.
went back to worksheet copy all column A and even delete it, then copy back the entries and nothing changed.. SKU's numbers on column B were not created..

Do I did something wrong?

thanks,
Eli

Hi Mr. Andrew..

I found the problem..it connected to the sub name at the top
it should be this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Cell As Range
Dim Test As Integer
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
For Each Cell In Target
With Cell.Offset(0, 1)
If IsEmpty(Cell) Then
.ClearContents
Else
.NumberFormat = "0000"
Do
.Value = Int(9999 * Rnd())
Test = WorksheetFunction.CountIf(.EntireColumn, .Value)
Loop Until Test <= 1
End If
End With
Next Cell
Application.EnableEvents = True
End Sub

so I correct it and now it works great !!

I will run a few checks to see if it function without any errors..

Thanks a lot dear Andrew, you did a great code !

Best Regards!

Eli

 

[Andrew Poulsom]

That code is in the module for ThisWorkbook and applies to all worksheets. The code I posted goes in the module for the Worksheet, eg Sheet1.

 

[eli_h]

That code is in the module for ThisWorkbook and applies to all worksheets. The code I posted goes in the module for the Worksheet, eg Sheet1.

Hi Dear Mr. Andrew,

Yes. you are absolutely right!

Thanks again and sorry for my impulsiveness. I am just new to excel
I am very grateful to you dear Andrew, you saved me a lot of work

All the best,

Eli