Solved

Unique Numbering Schema Routine in need of a tweek

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

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 31

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

617 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