Userform Incrementing Letter and Number Combinations in Sequence

[Nobu]

Hello!

I created UserForm for tracking purchases, in the Userform there is a combo box for selecting the type of purchase, 'DEBIT or CREDIT.

After selecting the purchase type there is a label box next to it that auto generates 'D0001' and 'C0001' before submitting.

The purchase numbers are going into column B and I want the form to pretty much look back at the sheet and add the next sequence numbers without any Duplicating.
Example:
D0001
C0001
D0002
D0003
C0002


This is the code I have now.

Sub FindNextPurchNum()
Dim Lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Manager")

myName = UCase(Left(Me.CmbobxPay, 1))
myNum = Format(myNum + 1, "0000")

Lastrow = Application.WorksheetFunction.Max(Sheet3.UsedRange.Columns(2))

Me.Lbl1 = myName & myNum

End Sub

 

[dmt32]

Hi
welcome to forum

Try this update to your code & see if does what you want

Sub FindNextPurchNum()
    Dim NextPO      As Long
    Dim ws          As Worksheet
    Dim myName      As String
   
    Set ws = Worksheets("Manager")
   
    myName = UCase(Left(Me.CmbobxPay, 1))
   
    NextPO = Application.CountIf(ws.Columns(2), myName & "*") + 1
   
    Me.Lbl1 = myName & Format(NextPO, "0000")
   
End Sub

Dave

 

[Nobu]

Hi
welcome to forum

Try this update to your code & see if does what you want

Sub FindNextPurchNum()
    Dim NextPO      As Long
    Dim ws          As Worksheet
    Dim myName      As String
  
    Set ws = Worksheets("Manager")
  
    myName = UCase(Left(Me.CmbobxPay, 1))
  
    NextPO = Application.CountIf(ws.Columns(2), myName & "*") + 1
  
    Me.Lbl1 = myName & Format(NextPO, "0000")
  
End Sub

Dave

Thank you Dave! It worked great!

 

[dmt32]

Thank you Dave! It worked great!

most welcome glad suggestion helped
Appreciate feedback

Dave