Random function in VBA

[Atalla]

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.

 

[Richie(UK)]

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>

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

 

[Joe Was]

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

 

[Atalla]

Thank you very for your prompt reply. I'll try it and let you know.

 

[maabbas]

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