Solved

Unique Numbering Schema Routine in need of a tweek

Posted on 2014-01-30
4
171 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

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

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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 create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

863 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

22 Experts available now in Live!

Get 1:1 Help Now