generate a random list with 3 options

[David Adamson]

Excel 2002

I am trying to generate a random list of three options "Poor", "Average",
"Good"

I do not want these numbers to change once they have been generated as they
will drive further options.

Tried to use something below and then use Vlookup to get the appropraite
response but its not what I am after.

Sub Generate_Year()
Dim i As Integer

For i = 1 To 100

With Worksheets("Year")
..Cells(3 + i, 1) = "=RANDBETWEEN(1,3)"
End With
Next i

End sub

I did try
..Cells(3 + i, 1) = Application.WorksheetFunction.RANDBETWEEN(1,3)
but it didn't work.

Any suggestions would be greatley appreciated.

 

[Bob Kilmer]

Doing this once?

Fill range with =INT(RAND() *3) + 1

Copy, PasteSpecial as values

 

[Bob Kilmer]

Hmmm.... I get #NAME? for =RANDBETWEEN(1,3) or any numbers even tho used
example found in in Help. XL2002, SP2, Win2K Pro. Curious.

 

[David Adamson]

You might not have the Analysis Tool Pack installed?

 

[David Adamson]

I have ended up creating this but can anyone suggest anything better?

---------------

Sub Generate_Year()
Dim i As Integer
Dim value As Long
Dim Data_Options As Range
Dim result As String

'set Vlookup range
With Worksheets("Options")
Set Data_Options = .Range("a4:b6")
End With

'create random numbers between 1 and 3
For i = 1 To 100
With Worksheets("Year")
..Cells(3 + i, 1) = Rnd() * (3 - 1) + 1

End With

'change random numbers to whole numbers
With Worksheets("Year")
value = .Cells(3 + i, 1)
value = Application.WorksheetFunction.Round(value, 0)
..Cells(3 + i, 1) = value
End With

'lookup up value and return name
With Worksheets("Year")
value = .Cells(3 + i, 1)
result = Application.WorksheetFunction.VLookup(value, Data_Options, 2, 0)
..Cells(3 + i, 1) = result
End With

Next i

End Sub

 

[Tom Ogilvy]

Sub AAA()
Dim varr(1 To 3)
varr(1) = "Poor"
varr(2) = "Average"
varr(3) = "Good"
For i = 1 To 100
With Worksheets("Year")
..Cells(3 + i, 1) = varr(Int(Rnd() * 3 + 1))
End With
Next

End Sub

 

[David Adamson]

Thanks for that Tom