Solved

Excel VBA - Numbering

Posted on 2012-03-15
11
466 Views
Last Modified: 2012-03-18
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.
0
Comment
Question by:desmondwkng
11 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37728102
Please upload a sample for efficiency.
0
 
LVL 6

Expert Comment

by:wshark83
ID: 37730124
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
 

Author Comment

by:desmondwkng
ID: 37732029
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
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 37734163
Use this formula in P3

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

Expert Comment

by:Saqib Husain, Syed
ID: 37734181
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Closing Comment

by:desmondwkng
ID: 37734198
thanks so much

cheers
0
 

Author Comment

by:desmondwkng
ID: 37735690
Dear ssaqibh

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


demsond
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37735961
Are you talking about the accepted answer or the modified code?
0
 

Author Comment

by:desmondwkng
ID: 37736172
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37736241
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
 

Author Comment

by:desmondwkng
ID: 37736425
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
I need to access elements from an existing child window to extrat info! 19 45
Create Form using Wizard 14 33
Highlighting cells in Excel 9 17
Vlookup formula error 15 11
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now