Solved

VBA Random Number

Posted on 2011-02-19
19
705 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
[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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
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…

732 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