generate a random list with 3 options
 

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.

generate a random list with 3 options
 

Doing this once?

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

Copy, PasteSpecial as values


"David Adamson" wrote in message
...
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.

generate a random list with 3 options
 

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" wrote in message
...
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.

generate a random list with 3 options
 

You might not have the Analysis Tool Pack installed?



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

generate a random list with 3 options
 

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

generate a random list with 3 options
 

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

--
Regards,
Tom Ogilvy



"David Adamson" wrote in message
...
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

generate a random list with 3 options
 

Thanks for that Tom