Solved

Excel VBA - Numbering

Posted on 2012-03-15
11
509 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 42

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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
 

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

707 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