Solved

Unique Numbering Schema Routine in need of a tweek

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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…

820 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