Random function in VBA

Atalla

New Member
Joined
Aug 28, 2002
Messages
5
Hi all,
I have a list of data (A1 to G20 containing ID, First_Name, Last_Name, etc..). I need a function or a procedure in VBA to randomaly select one record at a time (only the cells that contain the First_Name and Last_Name). And output the results on another sheet without having the random cells repeat themselves, i.e, the employee will be chosen just once. I managed to write the code in VBA Access but somehow I can't do it in Excel. I could link the code to a command button.
Thanks fo your help.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Atalla,

Welcome to the board. :)

This may be of use to you, its taken from Dave Hawley's site.
http://www.ozgrid.com/

User Defined Functions - Random Numbers

This UDF will generate x unique random numbers between any 2 numbers you specify. Many thanks to J.E. McGimpsey for modifying this to work on more than 10 numbers.

The Code


Function RandLotto(Bottom As Integer, Top As Integer, _
Amount As Integer) As String
Dim iArr As Variant
Dim i As Integer
Dim r As Integer
Dim temp As Integer

Application.Volatile

ReDim iArr(Bottom To Top)
For i = Bottom To Top
iArr(i) = i
Next i
For i = Top To Bottom + 1 Step -1
r = Int(Rnd() * (i - Bottom + 1)) + Bottom
temp = iArr(r)
iArr(r) = iArr(i)
iArr(i) = temp
Next i
For i = Bottom To Bottom + Amount - 1
RandLotto = RandLotto & " " & iArr(i)
Next i
RandLotto = Trim(RandLotto)
End Function



To use this UDF push Alt+F11 and go Insert>Module and paste in the code. Push Alt+Q and save. The Function will appear under "User Defined" in the Paste Function dialog box (Shift+F3). Use the Function in any cell as shown below.

=RandLotto(1,20,8)
This would produce 8 unique random numbers between 1 and 20

HTH


_________________<font color="blue"> «««<font color="red">¤<font color="blue"><font size=+1>Richie</font><font color="red">¤<font color="blue"> »»»</font>

caffeine_sample.gif
</gif>
This message was edited by Richie(UK) on 2002-08-29 15:05
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
This set of subs will random pick a name from a list of names. Hope it helps. JSW

Sub myRnd()
'Find a random name in a existing names list.
'By Joe Was, 6/27/2001.

Dim myOrder As Range
Dim myName
Dim mySelect As Variant
Randomize
'Note: The 20 below = the ending ROW of your names list.
' The 1 below = the starting ROW of your names list.
mySelect = Int((20 * Rnd) + 1)

'Note: The "A" below is the column where your names list is.
myName = Range("A" & mySelect)

'Put the answer in a cell
Worksheets("Sheet1").Range("C1") = myName

'Put the answer in a screen message box.
MsgBox "The selection is:" & Chr(13) & Chr(13) & myName
End Sub


Sub myErase()
Range("C1").Select
Selection.ClearContents
Range("C1").Select
End Sub


Sub myListR()
'Find a random name in a existing names list.
'Then adds it to an ongoing list.
'By Joe Was, 6/27/2001.

Dim myOrder As Range
Dim myName
Dim mySelect As Variant
Randomize
'Note: The "20" below is the ending ROW of your names list.
' The "1" below is the starting ROW of your names list.
mySelect = Int((20 * Rnd) + 1)

'Note: The "A" below is the column where your names list is.
myName = Range("A" & mySelect)

'Put the answer in the next empty cell, bottom of list, one column Right.
'Make sure column "B" has a data lable for the list to start under!
Worksheets("Sheet1").Range("B65536").End(xlUp).Offset(1, 0) = myName

End Sub
 

maabbas

Board Regular
Joined
Aug 11, 2011
Messages
201
This set of subs will random pick a name from a list of names. Hope it helps. JSW

Sub myRnd()
'Find a random name in a existing names list.
'By Joe Was, 6/27/2001.

Dim myOrder As Range
Dim myName
Dim mySelect As Variant
Randomize
'Note: The 20 below = the ending ROW of your names list.
' The 1 below = the starting ROW of your names list.
mySelect = Int((20 * Rnd) + 1)

'Note: The "A" below is the column where your names list is.
myName = Range("A" & mySelect)

'Put the answer in a cell
Worksheets("Sheet1").Range("C1") = myName

'Put the answer in a screen message box.
MsgBox "The selection is:" & Chr(13) & Chr(13) & myName
End Sub


Sub myErase()
Range("C1").Select
Selection.ClearContents
Range("C1").Select
End Sub


Sub myListR()
'Find a random name in a existing names list.
'Then adds it to an ongoing list.
'By Joe Was, 6/27/2001.

Dim myOrder As Range
Dim myName
Dim mySelect As Variant
Randomize
'Note: The "20" below is the ending ROW of your names list.
' The "1" below is the starting ROW of your names list.
mySelect = Int((20 * Rnd) + 1)

'Note: The "A" below is the column where your names list is.
myName = Range("A" & mySelect)

'Put the answer in the next empty cell, bottom of list, one column Right.
'Make sure column "B" has a data lable for the list to start under!
Worksheets("Sheet1").Range("B65536").End(xlUp).Offset(1, 0) = myName

End Sub

This code really works! the only thing what if I want the result in Sheet 2
 

Forum statistics

Threads
1,148,219
Messages
5,745,452
Members
423,952
Latest member
EduardoM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top