Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel VBA - Numbering

Posted on 2012-03-15
11
Medium Priority
?
527 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
New style of hardware planning for Microsoft Exchange server.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

609 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