Excel VBA - Numbering

Hi Experts,

I have worksheets that have serial numbers (Alphanumberic) assign to different orders for the month e.g : the ending order number is BB001001 for Feb and now I start the new worksheet for March and stats with the no. BB001002.

How I can do it with VBA.
desmondwkngAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
Please upload a sample for efficiency.
0
wshark83Commented:
assuming that you have a date in the data set then you can do this:

Sub CalcColB()
    Dim x As Integer
      For x = 2 To Cells(Rows.Count, "A").End(xlUp).Row
       
        If Month(Cells(x, 6).Value) = 1 Then ' if Jan then
            Cells(x, 7).Value = ""
        ElseIf Month(Cells(x, 6).Value) = 2 Then ' if Feb then
            Cells(x, 7).Value = "BB001001"
        ElseIf Month(Cells(x, 6).Value) = 3 Then ' if Mar then
            Cells(x, 7).Value = "BB001002"
        Else
        Cells(x, 7).Value = ""
        End If
    Next x
     
End Sub

and so on....
0
desmondwkngAuthor Commented:
ORDA-Agency.II.XLSDear All,

In the attached file, he macro "New_Month",  I'm trying to get the last number for March at Cell B17 (II01653) and add 1 there(should be II01654) which should be the 1st begining number for April.  
But because there is a "0" between the Alpha and the numeric part of the number, it does not work in my marco the the result become "II1654" instead of "II01654".

do you have an alterative solution for me.

thanks

Desmond

Below please find my marco

 Application.ScreenUpdating = False
    Range("B9").Select
    Selection.End(xlDown).Select
    Selection.Copy
    Range("P2").Select
    ActiveSheet.Paste
    Selection.Font.ColorIndex = 2
    Dim CurSheet As Worksheet
    Set CurSheet = ActiveSheet
    ActiveSheet.Copy Before:=Sheets(1)
    Range("A8:M8").Select
    Selection.AutoFilter
    Selection.AutoFilter
    Application.Goto Reference:="R8C3"
    Range("B9:I9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("P1").Select
    Selection.Copy
    Range("A9").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Selection.Font.ColorIndex = 0
    Selection.Style = "Comma"
    Range("J9:M9").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Selection.Font.ColorIndex = 0
    Selection.Style = "Comma"
    Application.Goto Reference:="R9C5"
    Application.Goto Reference:="R3C2"
    Selection.ClearContents
    ActiveCell = InputBox("Input New Month (MMM YYYY)")
    Dim ProdDate As String
    ProdDate = Format(Cells(3, 2), "YYMM")
    ActiveSheet.Name = Cells(1, 15) & ProdDate
    Range("P4").Select
    Selection.Copy
    Range("B9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Font.ColorIndex = 0
    Application.ScreenUpdating = True
    Application.Goto Reference:="R9C2"
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Saqib Husain, SyedEngineerCommented:
Use this formula in P3

=LEFT(O1,2)&TEXT(P3,"00000")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saqib Husain, SyedEngineerCommented:
I have also modified your code to remove redundant statements generated by the macro recorder. See if it works the same.

Sub New_Month()
    Application.ScreenUpdating = False
    Range("B9").End(xlDown).Copy Range("P2")
    Range("P2").Font.ColorIndex = 2
    Dim CurSheet As Worksheet
    Set CurSheet = ActiveSheet
    ActiveSheet.Copy Before:=Sheets(1)
    Range("A8:M8").AutoFilter
    Range("A8:M8").AutoFilter
    Range("B9:I" & Range("B9").End(xlDown).Row).ClearContents
    Range("P1").Copy Range("A9:A" & Range("A9").End(xlDown).Row)
    Range("A9:A" & Range("A9").End(xlDown).Row).Font.ColorIndex = 0
    Range("A9:A" & Range("A9").End(xlDown).Row).Style = "Comma"
    Range("J9:M9").Copy Range("J9:M" & Range("J9").End(xlDown).Row)
    Range("J9:M" & Range("J9").End(xlDown).Row).Font.ColorIndex = 0
    Range("J9:M" & Range("J9").End(xlDown).Row).Style = "Comma"
    Range("B3").ClearContents
    Range("B3") = InputBox("Input New Month (MMM YYYY)")
    Dim ProdDate As String
    ProdDate = Format(Cells(3, 2), "YYMM")
    ActiveSheet.Name = Cells(1, 15) & ProdDate
    Range("P4").Copy
    Range("B9").PasteSpecial Paste:=xlPasteValues
    Range("B9").Font.ColorIndex = 0
    Application.ScreenUpdating = True
End Sub

Open in new window

0
desmondwkngAuthor Commented:
thanks so much

cheers
0
desmondwkngAuthor Commented:
Dear ssaqibh

Sorry, it did not add "1" to the count at the next worksheek.


demsond
0
Saqib Husain, SyedEngineerCommented:
Are you talking about the accepted answer or the modified code?
0
desmondwkngAuthor Commented:
I just realise that the new formula has solve my "0" problem in my Alphanumeric number. But I need to add 1 count to the serial number when I go to the new month.
Hope I have explain the problem clearly.
0
Saqib Husain, SyedEngineerCommented:
I started with the II-1203 sheet which has II01653
I ran the macro and built the II-1204 sheet.
The first number in the new II-1204 sheet was II01654

Am I missing something?
0
desmondwkngAuthor Commented:
Sorry, I have mistakenly place the formula on P3 instead of P4, that's why it doesn't work
I've fixed it now,

thanks so much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.