Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA Random Number

Posted on 2011-02-19
19
Medium Priority
?
727 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 93

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

609 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