Solved

Unique Numbering Schema Routine in need of a tweek

Posted on 2014-01-30
4
172 Views
Last Modified: 2014-02-01
I have a unique numbering schema developed as a macro by Flyster.  However, as I have begun to use it and populate it, I'm getting "gaps" in the auto. numbering routine for some reason.  Can someone take a quick look at this and see how we can tweek it so it will again produce unique numbers when grouping FA (Functional Area),  VP (Value Proposition) and UC (Use Case)?

Thank you in advance!

B.
Unique-number-Scheme.xlsm
0
Comment
Question by:Bright01
  • 2
4 Comments
 
LVL 22

Assisted Solution

by:Flyster
Flyster earned 50 total points
ID: 39823259
Hi B. I'm a little perplexed here. I stepped through the code to see what happens when it got to row 35. It worked just like the previous row. I then deleted the numbers in column A, ran the macro and all rows populated. This was done 3 more times with the same results. See attached. What version of Excel are you using? I did this on 2007.

Paul
Unique-number-Scheme.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39823872
Although I also have Excel 2007 and it did not jump id's I believe some logic and declaration could be the cause of this behavior in other versions of Excel. Try this new Sub instead and check the results your side.

Sub UniqueNumberNEW()
Dim fa As Long, vp As Long, uc As Long, lr As Long, I As Long
Dim WS As Worksheet

Set WS = ActiveSheet

With Application
    .EnableEvents = False
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

lr = WS.Range("F" & Rows.Count).End(xlUp).Row
fa = 0
vp = 1
uc = 1


For I = 5 To lr
    Select Case WS.Range("F" & I).Value
    
        Case "FA"
            fa = fa + 1
            vp = 0
            uc = 0
        
        Case "VP"
            vp = vp + 1
            uc = 0
        
        Case "UC"
            uc = uc + 1
    End Select
    
    '---> Update Uniuenumber
    WS.Range("A" & I).Value = Format(fa, "00") & Format(vp, "00") & Format(uc, "00")
    
Next I

With Application
    .EnableEvents = True
    .DisplayAlerts = True
    .ScreenUpdating = True
End With


End Sub

Open in new window


Here are the main differences with the original routine:
1) By declaring
 Dim fa, vp, uc, lr, I As Double
you actually are decalring fa, vp, uc lr as variant and only I as double

I substituted it in mine by
Dim fa As Long, vp As Long, uc As Long, lr As Long, I As Long
which explicitly declares each and every variable to the correct type. (no need for double as they are all no decimal values)

2) Assuming the default worksheet is usually very dangerous as to running macros as if by chance the routine makes it that the focus is changed from the activesheet (here it would not likely be the case as you only have 1 sheet) but it is possible that if you moved that sample workbook to your production workbook that contains other macros and other sheets that this may very likely happens and when you use default sheet naming then something like
Range("A" & I).Value = Format(fa, "00") & Format(vp, "00") & Format(uc, "00")

could be updating an other sheet in the cell "A"I and not your intended sheet.

So I explicitly declared WS as worksheet and affected it to the current activesheet and used
instead of this line
Range("A" & I).Value = Format(fa, "00") & Format(vp, "00") & Format(uc, "00")

this line
WS.Range("A" & I).Value = Format(fa, "00") & Format(vp, "00") & Format(uc, "00")

to make sure it will always update my specific sheet.

3) the rest is embellishment that's all. also always make sure
.EnableEvents = True
.DisplayAlerts = True
.ScreenUpdating = True

are off when starting and on when finishing to make sure nothing intervene with the macro while running.

Regards
gowflow
Unique-number-Scheme-V01.xlsm
0
 
LVL 29

Accepted Solution

by:
gowflow earned 450 total points
ID: 39824042
Actually this is a more secure version as it compare the lowercase of the Cell in Col F as if inadvertently Col F items are not in upper case you may get wrong numbering also. This will ensure that regardless how code is written in Col F it will not miss numbering.

gowflow
Unique-number-Scheme-V02.xlsm
0
 

Author Closing Comment

by:Bright01
ID: 39827178
Thanks guys!  Now it works.  Now it's better!

B.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

813 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

12 Experts available now in Live!

Get 1:1 Help Now