Solved

VBA Random Number

Posted on 2011-02-19
19
682 Views
Last Modified: 2012-05-11
Hello,
I am trying to create a user defined function to assign a UID random number using concatenation and random number
functions for 10 people.  the output in excel should look like this U########.  
0
Comment
Question by:maudette
  • 9
  • 9
19 Comments
 

Author Comment

by:maudette
ID: 34933888
I also want to know how to call this in Excel once I have created the code in VBA
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34933913
I have made it more generic. Paste this code in a module

Public Function RandNo(strChar As String, Numb As Long) As String
    RandNo = strChar & Int(Rnd * (10 ^ Numb))
End Function

Open in new window


To call from Excel, simply type this formula

=RandNo("U",8)

I have made it generic so that you can test it with other alphabets with different sizes :)

Sid
0
 

Author Comment

by:maudette
ID: 34933938
Thanks Sid!  I have one question, I put this the way your suggested in the module and called it the same way.  I need them to all have the same amount of numbers tied to the U, is there a way to do this?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34933953
They will not have the same numbers as Rnd will generate a random number everytime.

Sid
0
 

Author Comment

by:maudette
ID: 34933976
Is there a way to have the same amount of numbers everytime?  such as a random number between 99999999 and 10000000 with U. such as everytime it will be U and 8 numbers.  
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34933984
How are the 10 users connected to the same workbook? Is the workbook shared?

Sid
0
 

Author Comment

by:maudette
ID: 34933998
it really doens't matter the number of users, what I am trying to do is create a unique ID for a set of students a unique identifier using VBA.  I used 10 users as an example to say I need 10 unique ID's, with a U in front of it.  Does that make sense?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34934011
Oh you mean like

U46870011
U29816544
U62269669
U64782118
U26379293
U27934205
U82980161
U82460212
U86261934
U79048001

Sid
0
 

Author Comment

by:maudette
ID: 34934014
yes exactly!
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34934019
maudette,

While it is highly unlikely, using a single UDF call per user leaves open the possibility of duplication.  On that basis, I should think a macro that spits out a specified number of unique IDs, or a UDF returning such an array, would be a better fit.

Please clarify your requirements on such issues as the length of the string, the range of allowed numbers, whether to pad with leading zeroes, etc.

Patrick
0
 

Author Comment

by:maudette
ID: 34934033
Patrick,
I was tasked with creating a UDF by utilizing concat and RND functions to develop a Unique ID with a U infront and 8#'s attached such as the output example that Sid showed.  
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34934037
This will give you 10 unique numbers.

Sub Sample()
    Dim Ret As String
    
    For i = 1 To 10
        Debug.Print "U" & Format(Int(Rnd * 100000000), "00000000")
    Next i
End Sub

Open in new window


Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34934042
If you wish, you may output these numbers to Excel Sheet.

Sid
0
 

Author Comment

by:maudette
ID: 34934052
Sid, is this a UDF?  How would I call this?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34934053
Ok where do you want the output?

Sid
0
 

Author Comment

by:maudette
ID: 34934087
this is what I am using as a spreadsheet to create the UID... I need to be able to call a specific UID for the following people by creating a UDF that uses concat and RND that takes on the form U########.  I then need to place them in two groups by birthday.  Group 1 is anyone whose birthday between January 1 and June 30 of any year using if/then statement.  Group 2 is anyone whose birthday is between July 1-Sept. 30 of any year using selectcase statement

Last Name      First Name      Birthday      UID      Project 1 team      Project 2 team
Queen      Victor      9/25/1973                  
Lame      Patty      8/2/1982                  
Fence      Noah      7/26/1986                  
King      Joe      12/22/1973                  
Xtre      Dean      11/25/1970                  
Sew      Lane      12/20/1979                  
Due      Paige      1/13/1971                  
Payne      Jane      5/7/1984                  
Coe      John      6/1/1975                  
Dean      Adam      5/2/1983                  
Cute      Sue      5/7/1978                  
Free      Monica      4/3/1987                  
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34934103
Then what I suggested in the first is what you need :)

Paste this code in a module.

Public Function RandNo(strChar As String, Numb As Long) As String
    RandNo = strChar & Format(Int(Rnd * 10 ^ Numb), String(Numb, "0"))
End Function

Open in new window


And Just call this formula in every cell.

=RandNo("U", 8)

Sid
0
 

Author Comment

by:maudette
ID: 34934131
Thanks,
I am new to this and may not have been asking the right question.  This is how I got it to work.  

Function UID()
UID = "U" & Int((99999999 - 10000000) * Rnd + 10000000)
End Function

and then called =UID() in the cell.  

Any tips on how to separate the students into the two groups I mentioned above?
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 125 total points
ID: 34934142
Thanks,
I am new to this and may not have been asking the right question.  This is how I got it to work.  

You asked the right question and I gave the right answer :)

Replace your function by my function in the module and replace =UID() in the cell with =RandNo("U", 8) ;-)

As far as the 2nd question is concerned that should go as a new post :)

Sid
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

Suggested Solutions

Title # Comments Views Activity
Excel VBA - Returning results from batch file 9 67
DBF to ... Converter 5 40
Deleting Unselectable Lines in Word 2013 2 33
File not loading into PowerPivot 4 9
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
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 …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

910 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

23 Experts available now in Live!

Get 1:1 Help Now