Random Names

[DCSwearingen]

We are a food production facility and one of our outside auditors has
indicated we need to institute a random locker inspection to comply
with food safety requirements.

After a quick Internet search I found this routine:
------------------
Sub GetRandom()
Dim iRows As Integer
Dim iCols As Integer
Dim iBegRow As Integer
Dim iBegCol As Integer
Dim J As Integer
Dim sCells As String

Set TempDO = New DataObject

iRows = Selection.Rows.Count
iCols = Selection.Columns.Count
iBegRow = Selection.Row
iBegCol = Selection.Column

If iRows < 16 Or iCols > 1 Then
MsgBox "Too few rows or too many columns"
Else
Randomize Timer
sCells = ""
For J = 1 To 15
iWantRow = Int(Rnd() * iRows) + iBegRow
sCells = sCells & _
Cells(iWantRow, iBegCol) & vbCrLf
Next J
TempDO.SetText sCells
TempDO.PutInClipboard
End If
End Sub
-------------------
However, when I try to run the routine, I get a compile error saying
User Defined Type Not Defined for "New DataObject".

I am not familiar with using the DataObject and there is not much in
the Excel VBA Help files that explains how to declare this...

 

[Jim Cone]

You don't have to use code to accomplish this.
Put your list of names or locker numbers in a column with room
for a header formula. If you data is in C2:C51 then in C1 enter...
=OFFSET(C1,RAND()*51,0)
Every time the sheet calculates or you press F9 you get another
random selection.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"DCSwearingen"
wrote in message
We are a food production facility and one of our outside auditors has
indicated we need to institute a random locker inspection to comply
with food safety requirements.

After a quick Internet search I found this routine:
------------------
Sub GetRandom()
Dim iRows As Integer
Dim iCols As Integer
Dim iBegRow As Integer
Dim iBegCol As Integer
Dim J As Integer
Dim sCells As String

Set TempDO = New DataObject

iRows = Selection.Rows.Count
iCols = Selection.Columns.Count
iBegRow = Selection.Row
iBegCol = Selection.Column

If iRows < 16 Or iCols > 1 Then
MsgBox "Too few rows or too many columns"
Else
Randomize Timer
sCells = ""
For J = 1 To 15
iWantRow = Int(Rnd() * iRows) + iBegRow
sCells = sCells & _
Cells(iWantRow, iBegCol) & vbCrLf
Next J
TempDO.SetText sCells
TempDO.PutInClipboard
End If
End Sub