Advice to change or speed up the way this code operates at present

[ipbr21054]

The code in question is supplied below.

On my worksheet i have customers names in column B

I start by opening my userform & i enter the customers name in TextBox2 Example TOM JONES.
The code looks down column B on my worksheet for all the TOM JONES.

Each time TOM JONES makes a purchase 3 digits are added to the end of his name,So lets say in this case the search has found TOM JONES 001 & TOM JONES 002

Back to userform now, When i leave TextBox2 on this occurance 003 will be added to TOM JONES in TextBox2, I complete all the other TextBoxes & the values are sent to my worksheet.

This all works fine & never an issue.
Now at present the last row with values on my worksheet is 2316

The issue i have is that one particular customer has made numerous purchases overtime & his 3 digits are now 105
The issue is when i go to leave TextBox2 the search of which i assume looks from row 2316 right back until row 7 is now taking some time before his next 3 digits are added to his name.
Im experiencing lag with this one customer.

Can we alter the code below to speed things up or do you think the code just needs to be altered for another to overcome this lag
First time customers or customers that only have 002 003 004 etc after their name doesnt seem to be an issue & leaving TextBox2 is pretty much no problem.

This customers buys from me pretty much every month so maybe restrict & only this customer to have the code search maybe back the last 100 rows each time.
So maybe some kind of IF code for JOES BLOGGS etc etc to check back 100 rows or Exit Sub if its another customer, Just a thought



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

 

[NoSparks]

Will this work for you ?

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim fndRng As Range
    Dim findString As String
    Dim wsPostage As Worksheet
    Dim purchaseNum As Long

findString = Me.TextBox2.Value
If Len(findString) = 0 Then Exit Sub

Set wsPostage = ThisWorkbook.Worksheets("POSTAGE")

With wsPostage
    Set fndRng = .Range("B:B").Find(What:=findString, LookIn:=xlValues, LookAt:=xlPart, _
                     SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
    If Not fndRng Is Nothing Then
        purchaseNum = Right(fndRng, 3)
    Else
        purchaseNum = 0
    End If
End With

TextBox2.Value = findString & Format(purchaseNum + 1, " 000")

End Sub

 

[ipbr21054]

No lag at all using that code.

Many Thanks

 

[CephasOz]

Just be aware that this code relies on the data always being sorted by the key of customer name and sales number. If you ever use a different sort order, you run the risk of generating a key that already exists. If that is the case, you could put a sort in your code that displays the form, but before the .Show.

 

[ipbr21054]

My original or the advised one ?
I never sort anything as the worksheet is from top to bottom & each customers number is increased by 1 so 001 002 003 004 & so on.
So im sure all is fine.

 

[CephasOz]

I was referring to the code kindly supplied by @NoSparks. Since your data is always in the right order, there is no issue ... but things change, so be ready for that by inserting the sort command now while you're thinking about it. It won't make any difference to the functionality, but it will help to ensure that you always get the right answer.

 

[ipbr21054]

OK i will await @NoSparks to come online & advise.
Thanks

 

[NoSparks]

Good morning @ipbr21054,
my suggestion is based on previous posts and file you had linked to

I never sort anything as the worksheet is from top to bottom & each customers number is increased by 1 so 001 002 003 004 & so on.
So im sure all is fine.

and it is, but as the saying goes Never say Never, so I wouldn't dispute what @CephasOz is saying

 

[ipbr21054]

I will rephrase what I said.

From my userform I send the values to the next row after the last row with values.

This is what I mean by I don’t or have no need to sort anything.

If you think adding some code to just cover myself then please advise.