Identify & edit code

[ipbr21054]

Morning,

I am not sure which part of the code below adds a following digit of 1 each time the same name is entered.
If the name exists then a 2 would be added to the end of that name.
Should then that name be entered again then this time it is followed by 3,in the future 4,5,6 etc etc

Can we identify & edit the part of the code that applies this as i would like to have it apply 002 or 003 etc etc as opposed to the 2,3 etc that is currently happening.

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)    Dim fndRng As Range, findString As String, i As Integer


If Me.TextBox2.Value = "" Then Exit Sub
findString = Me.TextBox2.Value


With Sheets("POSTAGE").Range("B:B")
    Set fndRng = .Find(What:=findString, LookIn:=xlValues, lookat:=xlWhole, _
                   SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                   
    If Not fndRng Is Nothing Then
        'what was entered already exists - alter the name until not found
        For i = 2 To 20
            findString = Me.TextBox2.Value & " " & i
            Set fndRng = .Find(What:=findString, LookIn:=xlValues, lookat:=xlWhole, _
                       SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
            If fndRng Is Nothing Then Exit For
        Next i
        
        'message saying what name should be
        'MsgBox "The name to use is " & findString
        
        'enter that name into textbox 2
        With Me.TextBox2
            .Value = findString
            .SelStart = 0
            .SelLength = Len(.Text)
            .SetFocus
        End With
        
        'cancel moving out of text box
        Cancel = True
    End If
End With

 

[ipbr21054]

Have you tried post #4 ?

Yes i did but a received an error of Method or data member not found.
I thought you forgot the . at the start of the code so this message popped up

If i leave the . out then then if i type a name that is already in my list then TextBox2 highlights the typed name and then nothing else happens.

 

[Joe4]

At present if i sort the list i see the below example.
JOE 2
JOE 3
JOE 4
When it reaches JOE 10 the order is then all over the place due to NO leading zeros etc,hence why i am trying to have it like JOE 002 JOE 003 etc

On the userform in TextBox2 i would enter JOE the code quickly looks at the list and sees that the last entry for JOE is JOE 7 so the userform textbox changes the typed JOE to JOE 8
I then complete the rest of the form & the data from the form is sent to the worksheet.

Over time when i enter the customers name in TextBox2 i dont know if they are entered 1,2,3,4 times etc on the worksheet hence this code doing it for me.

Do this answer your question ?

Thanks

Basically do what the code is currently doing but somehow format it,so dont just add a 2 or 3 after an already typed name into TextBox2 BUT add 002,003 etc

There seems to be a disconnect here, if your data currently looks like:

JOE 2
JOE 3
JOE 4

But you want to look for and add values like:

JOE 002
JOE 003
JOE 004

Seems to me like you need to commit to one method or the other, and if committing to the new method showing the leading zeroes, then you need to go back and update all of your current existing data so it is in that format.

 

[footoo]

Yes i did but a received an error of Method or data member not found.
I thought you forgot the . at the start of the code so this message popped up

If i leave the . out then then if i type a name that is already in my list then TextBox2 highlights the typed name and then nothing else happens.

??? What . at the start ?

Did you replace this :

findString = Me.TextBox2.Value & " " & i

With this :

findString = Me.TextBox2.Value & Format(i, " 000")

 

[ipbr21054]

I have gone through the list of customers name and changed them all manually.

So what used to be JOE 2 is now JOE 002
That same customer then purchased again so his record was entered as JOE 3 but i have now changed it to JOE 003

I have done the above for ALL the 1000 names name.

So from now on & into the future when i enter say JOE in the userform TextBox2 the code does the correct thing in respect of looking for the last entry for JOE & then automatically enters in the TextBox2 say JOE 007

With me so far ?

This is where it will fails because i dont want the format as JOE 7 BUT JOE 007

I cant be adding names to the worksheet like a sinhgle number as i will need to do the manual foramating all over again.

The code at present works 100% as i want it BUT now have it enter as 002 003 004 etc etc

 

[ipbr21054]

I was sidetracked and post #13 seems to have done it.

I will reply back soon with an update

Sorry for confusion

 

[ipbr21054]

Hi,
Sorry again for confusion.

i CAN CONFIRM THAT #13 DID THE TRICK AND WORKS GREAT

tHANKS VERY MUCH ALL

 

[Joe4]

Glad you got it all sorted out.