generating a whole random # from a list

Hello –
I have a list of numbers in A1:A10, 1 through 10.
From that list of numbers, what I’ve got below is partially working, but I
cannot seem to find how to generate a whole random # from that list…


Sub GenRand2()
Dim myRange As Range
Dim rO As Integer
Dim Result As Double

Set myRange = Worksheets("Sheet1") _
.Range("A1").CurrentRegion
rO = myRange.Rows.Count

Result = rO * Rnd()
MsgBox Result

End Sub


Sincerely,
Arturo

Sub GenRand2()
Dim myRange As Range
Dim rO As Integer
Dim Result As Double
Set myRange = Worksheets("Sheet1") _
.Range("A1").CurrentRegion
rO = myRange.Rows.Count
Randomize
Result = Int((rO * Rnd) + 1)
MsgBox Result
End Sub

"Arturo" <[email protected]> schreef in bericht
news:[email protected]...
> Hello -
> I have a list of numbers in A1:A10, 1 through 10.
> From that list of numbers, what I've got below is partially working, but I
> cannot seem to find how to generate a whole random # from that list.
>
>
> Sub GenRand2()
> Dim myRange As Range
> Dim rO As Integer
> Dim Result As Double
>
> Set myRange = Worksheets("Sheet1") _
> .Range("A1").CurrentRegion
> rO = myRange.Rows.Count
>
> Result = rO * Rnd()
> MsgBox Result
>
> End Sub
>
>
> Sincerely,
> Arturo

Assuming (a) by whole random # you mean a number from the myRange=20
range, and (b) CurrentRegion consists of a single column, use

Result =3D myRange.Cells(Fix(rO * Rnd() + 1), 1)


--=20
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,=20
[email protected] says...
> Hello =E2=A4=3D3F
> I have a list of numbers in A1:A10, 1 through 10.
> From that list of numbers, what I=E2=A4=3D3Fve got below is partially wor=
king, but I=20
> cannot seem to find how to generate a whole random # from that list=E2=A4=
=3D3F
>=20
>=20
> Sub GenRand2()
> Dim myRange As Range
> Dim rO As Integer
> Dim Result As Double
>=20
> Set myRange =3D Worksheets("Sheet1") _
> .Range("A1").CurrentRegion
> rO =3D myRange.Rows.Count
> =20
> Result =3D rO * Rnd()
> MsgBox Result
>=20
> End Sub
>=20
>=20
> Sincerely,
> Arturo
>=20