Generating alphanumeric code

[Closed]
Report
Posts
1
Registration date
Wednesday July 8, 2015
Status
Member
Last seen
July 9, 2015
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
Sir,

Can anybody help me in writing vba code for excel to generate alphanumeric code having first entry as L,P,G,T(based on a condition), next four numbers starting from 0001 and last four as mmyy format for the current month and year. The code should be unique and increasing in pattern and should serve 20-25 users working at a time connected through internet/intranet/LAN.

Rgds,

Pankaj

2 replies

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Pankaj,

Could let us know how you want this to work?

Like what determines if the string should start with L, P, G or T? Where should this string be placed?

Best regards,
Trowa
Thanx for your concern. Actually, I want the code to be generated in a separate Excel Sheet, on sharing mode from where the same would be imported by the user, as & when required. The generation of code would be based on Transaction type, which may be Cash(C), Party Payment(O), or Tour Payment(T) and L for Bank payment. Of course the generation would be based on the last number generated and string be taken from the Type of transaction user wants. The same would be generated in the Shared folder and imported to the user's file and from there, others' field would be populated in the shared folder(original folder) through vlookup or match formula of excel. Hope I am clear.
Rgds,
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Pankaj,

Not entirely clear, so let's start with the following:
First I made my sheet look like this:


Then running the code below and entering either C, O, T or L when asked for will generate the "code" as requested.
Sub RunMe()
Dim pType As String
Dim mNumber, cLength As Integer

pType = InputBox("Please enter Payment Type letter:")

If pType = "C" Then
    mNumber = Mid(Range("A" & Rows.Count).End(xlUp), 2, 4) + 1
    cLength = Len(mNumber)
    Do Until cLength = 4
        cLength = cLength + 1
        mNumber = "0" & mNumber
    Loop
    Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "C" & mNumber & Format(Now, "MMYY")

ElseIf pType = "O" Then
    mNumber = Mid(Range("B" & Rows.Count).End(xlUp), 2, 4) + 1
    cLength = Len(mNumber)
    Do Until cLength = 4
        cLength = cLength + 1
        mNumber = "0" & mNumber
    Loop
    Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = "C" & mNumber & Format(Now, "MMYY")

ElseIf pType = "T" Then
    mNumber = Mid(Range("C" & Rows.Count).End(xlUp), 2, 4) + 1
    cLength = Len(mNumber)
    Do Until cLength = 4
        cLength = cLength + 1
        mNumber = "0" & mNumber
    Loop
    Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = "C" & mNumber & Format(Now, "MMYY")

ElseIf pType = "L" Then
    mNumber = Mid(Range("D" & Rows.Count).End(xlUp), 2, 4) + 1
    cLength = Len(mNumber)
    Do Until cLength = 4
        cLength = cLength + 1
        mNumber = "0" & mNumber
    Loop
    Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = "C" & mNumber & Format(Now, "MMYY")

End If

End Sub


Try it and let me know your thoughts.

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.