# Random function in VBA

#### Atalla

##### New Member
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.

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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

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

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

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

Replies
5
Views
164
Replies
12
Views
186
Replies
10
Views
205
Replies
3
Views
508
Replies
2
Views
532

1,218,576
Messages
6,143,314
Members
450,477
Latest member
teresab543

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

### Which adblocker are you using?

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

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