Advice needed please to avoid duplicates.

[ipbr21054]

On my worksheet i enter customers in column A
Over time i get repeat buyers of which i cant have the same name in the cell so it needs to be followed by a number etc.

So here is the example in the screens hot supplied.

I would advice on how i can overcome this.
I think the best thing to do would be to show a msgbox with the latest number should it be a duplicate ?

Some info for you.
Column A is where the customers name are.
The range will always be A6 then down the page.

So lets assume BO DEREK isnt in the list.
I type BO DEREK in cell A6
I then leave the cell & i then see BO DEREK 001 so this is working good.

So lets assume BOB BROWN is in the list 3 times.
BOB BROWN 001
BOB BROWN 002
BOB BROWN 003

I type BOB BROWN in cell A6 & the code checks column A for a match,the match is found and a Msgbox is shown advising me of the next number to use so BOB BROWN 004

OR better still if i type BOB BROWN & leave the cell have the codee just alter it to BOB BROWN 004 without the need for a msgbox

WHAT DO YOU THINK
THANKS

 

[ipbr21054]

Does this help you & to save time.

I just remembered that i use something like this on my userform for when i transfer to another worksheet.

Can we edit the code so it works when i leave the cell A6
It was currently on a userform

See what you think

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim fndRng As Range
    Dim findString As String
    Dim i As Integer
    Dim wsPostage As Worksheet
   
    findString = Me.TextBox2.Value
    If Len(findString) = 0 Then Exit Sub
   
    Set wsPostage = ThisWorkbook.Worksheets("POSTAGE")
    i = 1
    Do
        Set fndRng = Nothing
        Set fndRng = wsPostage.Range("B:B").Find(What:=findString & Format(i, " 000"), _
                                                    LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, _
                                                    SearchDirection:=xlNext, MatchCase:=False)
        If Not fndRng Is Nothing Then
            i = i + 1
            Cancel = True
        End If
    Loop Until fndRng Is Nothing
   
    Me.TextBox2.Value = findString & Format(i, " 000")
    Cancel = False
   
End Sub

The range is always column A
The cell where i type the customers name is always cell A6
The worksheet is called DATABASE

 

[Sharid]

See if something like this might help, Random Employee Number Generator, Give then a unique ID number so you can search for them easier as you can have 100s of
Jane Doe, but each one will have their own ID number

 

[ipbr21054]

Thanks but i wish to continue with the code supplied above.
It needs to be edited to run on a worksheet

 

[ipbr21054]

I have this now on a button.
Can you advise what ive missed out as when i run the command button the send the value from textbox1 nothing is entered in cell A6

I type in TextBox1 BOB SMITH & when i leave the textbox i see it change to BOB SMITH 001
I press the transfer button.
A new row is created at row 6 BUT nothing BOB SMITH 001 isnt entered in cell A6

Private Sub CloseForm_Click()
Unload DatabaseUserForm
End Sub
Private Sub TextBox1_Change()
    TextBox1 = UCase(TextBox1)
End Sub
Private Sub DatabaseSheetTransferButton_Click()
Cancel = 0
If TextBox1.Text = "" Then
    Cancel = 1
    MsgBox "MUST ENTER CUSTOMERS NAME", vbCritical, "DATABASE USER FORM NAME TRANSFER"
    TextBox1.SetFocus
    
End If

If Cancel = 1 Then
Exit Sub
End If

Dim i As Long
Dim x As Long
Dim ctrl As Control
Dim LastRow As Long
LastRow = ThisWorkbook.Worksheets("DATABASE").Cells(Rows.Count, 1).End(xlUp).Row
    
With ThisWorkbook.Worksheets("DATABASE")
    .Cells(LastRow + 1, 2).Value = TextBox1.Text

End With
Rows("6:6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A6:Q6").Borders.LineStyle = xlContinuous
Range("A6:Q6").Borders.Weight = xlThin
Range("A6:Q6").Interior.ColorIndex = 6
Range("$Q$6").Value = "'NO NOTES FOR THIS CUSTOMER"
Range("$Q$6").HorizontalAlignment = xlCenter
Sheets("DATABASE").Range("A6").Select
Unload DatabaseUserForm

End Sub
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim fndRng As Range
    Dim findString As String
    Dim i As Integer
    Dim wsPostage As Worksheet
    
    findString = Me.TextBox1.Value
    If Len(findString) = 0 Then Exit Sub
    
    Set wsPostage = ThisWorkbook.Worksheets("DATABASE")
    i = 1
    Do
        Set fndRng = Nothing
        Set fndRng = wsPostage.Range("A:A").Find(What:=findString & Format(i, " 000"), _
                                                    LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, _
                                                    SearchDirection:=xlNext, MatchCase:=False)
        If Not fndRng Is Nothing Then
            i = i + 1
            Cancel = True
        End If
    Loop Until fndRng Is Nothing
    
    Me.TextBox1.Value = findString & Format(i, " 000")
    Cancel = False
    
End Sub

 

[ipbr21054]

Ive found the error.
End of code was the issue.
Name was added to the last row