Generating a conditional sequence number - Excel VBA

[CakzPrimz]

Hi,

I have to create a sequence number for employee's cooperative loan. The sequence number should be written in Column B, with an order as below :
- start with "L"
- Running year "2018"
- Running month "02"
- and sequence number 0001
So it will be like : L2018-02-0001 and for the next loan, will be L2018-02-0002.

But I don't know how to do it with macro. I hope this forum could help me (again and again).

Thanks in advance.

 

[vcoolio]

Hello CakzPrimz,

Lets assume:-

- Cell A1 has the current date in it (you'll need a date somewhere for this to work successfully).
- Column A has the employee names in it.

Based on the assumptions, the following code should work for you:-

Sub CreatSequencedNos()

    Dim lr As Long

lr = Range("A" & Rows.Count).End(xlUp).Row

    With Range("B2:B" & lr)
        .Formula = "=""L"" & Year($A$1) & ""-"" & ""0"" & Month($A$1) & ""-"" & ""000"" & Row()-1"
    End With

End Sub

The code inserts a formula in each cell in Column B for as long as there is a name in Column A. The formula creates the sequenced numbers.

I hope that this helps.

Cheerio,
vcoolio.

 

[Takae]

Assuming you have the date on A1.

B2="L"&YEAR(A1)&"-"&TEXT(MONTH(A1),"mm")&"-"&REPT(0,4-LEN(ROW()-1))&ROW()-1

Copy down.

Hope this helps.

 

[CakzPrimz]

Dear Takae and vcoolio,

I've tried the codes already, the code given by vcoolio works well but the the code from Takae, miss in month.
The result of Takae's code is : L2018-01-0000, the number should be L2018-02-0000 since the running month is February.

I am really sorry, I should explaining from the very beginning:
The sequence number should shown up in Textbox1, every time the userform is open. So the code it will written in Userform_Initialize event.
With condition as below:
1. If no sequence number found in column B at all (sheets "EMPLOY") then TextBox1.value = L2018-02-0000
2. In contrary, if any sequence number found in column B (sheets "EMPLOY") then it will generate a sequence number as my question above.

I beg you pardon, for this inconvenience. Thanks so much for your attention and helping hands.

Best regards.

 

[CakzPrimz]

Dear Takae and vcoolio,

In the userform, I have a TextBox7 which contain today's date

Me.TextBox7.Value = Format(Now, "dd-mmm-yyyy")

Thanks

 

[CakzPrimz]

Dear Takae and vcoolio,

So far, I have only the codes below but I am stuck in how to generate the sequence number, behind L2018-02-000 based on columns B

Private Sub UserForm_Initialize()
    Me.TextBox7.Value = Format(Now, "dd-mmm-yyyy") 'Today Date
    Dim s As String
    s = "L" & Year(TextBox7.Value) & "-" & Month(TextBox7.Value) & "-" & "000"
    TextBox19.Value = s
End Sub

My header is row #2 .

Best Regards,




Best Regards

 

[Takae]

Header is #2 , the start should be row #3 ?
Please try it.

Private Sub UserForm_Initialize()
    Me.TextBox7.Value = Format(Now, "dd-mmm-yyyy") 'Today Date
    Dim s As String, m As String, z
    Dim LR As Long, x As String
     z = Format(Now, "dd-mmm-yyyy") 'Today Date
    LR = Sheets("EMPLOY").cells(Rows.count, 2).End(xlUp).Row - 1
    x = LR
    m = Month(Me.TextBox7.Value)
    s = "L" & Year(z) & "-" & WorksheetFunction.Rept(0, 2 - Len(m)) & Month(Me.TextBox7.Value) & "-" & WorksheetFunction.Rept(0, 3 - Len(x)) & LR
    TextBox19.Value = s
End Sub

 

[CakzPrimz]

Dear Takae,

Thanks for everything, it is great !!! The code works so fine !!! Problem solved.
Really appreciate for your time and effort.

Thank you