Previous Topic Next Topic

Generating alphanumeric code

Closed
Pankaj2411 Posts 1 Registration date Wednesday July 8, 2015 Status Member Last seen July 9, 2015 - Jul 9, 2015 at 05:54 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jul 13, 2015 at 12:15 PM
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
Related:

2 responses

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jul 9, 2015 at 11:18 AM
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
0
Pankaj
Jul 13, 2015 at 08:00 AM
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,
0
Answer 2 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jul 13, 2015 at 12:15 PM
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.
0

Similar discussions

lava hard reset & factory reset
AVAY -
Har -

3 responses
Unlock code
Wwe -
Sahilkarate -

1 response
Tetris unlock the code samsung
Mohit -
HelpiOS -

1 response