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.

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.

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.

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.

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

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

Thanks for that Tom