• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 735
  • Last Modified:

VBA Random Number

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
maudette
Asked:
maudette
  • 9
  • 9
1 Solution
 
maudetteAuthor Commented:
I also want to know how to call this in Excel once I have created the code in VBA
0
 
SiddharthRoutCommented:
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
 
maudetteAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
SiddharthRoutCommented:
They will not have the same numbers as Rnd will generate a random number everytime.

Sid
0
 
maudetteAuthor Commented:
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
 
SiddharthRoutCommented:
How are the 10 users connected to the same workbook? Is the workbook shared?

Sid
0
 
maudetteAuthor Commented:
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
 
SiddharthRoutCommented:
Oh you mean like

U46870011
U29816544
U62269669
U64782118
U26379293
U27934205
U82980161
U82460212
U86261934
U79048001

Sid
0
 
maudetteAuthor Commented:
yes exactly!
0
 
Patrick MatthewsCommented:
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
 
maudetteAuthor Commented:
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
 
SiddharthRoutCommented:
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
 
SiddharthRoutCommented:
If you wish, you may output these numbers to Excel Sheet.

Sid
0
 
maudetteAuthor Commented:
Sid, is this a UDF?  How would I call this?
0
 
SiddharthRoutCommented:
Ok where do you want the output?

Sid
0
 
maudetteAuthor Commented:
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
 
SiddharthRoutCommented:
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
 
maudetteAuthor Commented:
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
 
SiddharthRoutCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now