Solved

Unique Numbering Schema Routine in need of a tweek

Posted on 2014-01-30
4
174 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
[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
  • 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 30

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 30

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

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.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 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