Draw a random number from list of values

[NZAS]

This is to do random draw from the numbers that are in the list
I have a list of numbers from which I want to return one of those but randomly.
Return a random number for numbers in column A cells 1 to where even the list could finish. The end could be any number of cells used for the hole numbers. The holes used are determined by what has been scored on the holes and these are manually typed in the column A

Holes
1
7
15
3
6

Cheers
NZAS

 

[lrobbo314]

Book1
	A	B	C
1	Holes		Random Pick
2	1		3
3	7
4	15
5	3
6	6
Sheet1

Cell Formulas
Range	Formula
C2	C2	=INDEX($A$2:$A$6,RANDBETWEEN(1,COUNTA($A$2:$A$6)))

 

[NZAS]

Book1
	A	B	C
1	Holes		Random Pick
2	1		3
3	7
4	15
5	3
6	6
Sheet1

Cell Formulas
Range	Formula
C2	C2	=INDEX($A$2:$A$6,RANDBETWEEN(1,COUNTA($A$2:$A$6)))

Could this be done using VBA

Book1
	A	B	C
1	Holes		Random Pick
2	1		3
3	7
4	15
5	3
6	6
Sheet1

Cell Formulas
Range	Formula
C2	C2	=INDEX($A$2:$A$6,RANDBETWEEN(1,COUNTA($A$2:$A$6)))

Book1
	A	B	C
1	Holes		Random Pick
2	1		3
3	7
4	15
5	3
6	6
Sheet1

Cell Formulas
Range	Formula
C2	C2	=INDEX($A$2:$A$6,RANDBETWEEN(1,COUNTA($A$2:$A$6)))

 

[NZAS]

Could this be set up with using user form and the vba

 

[Rick Rothstein]

I am not sure what you have in mind for your UserForm, so I am giving you a function that you can call in an Excel cell or from within your own VBA code. The function assumes the values are in Column A and the list starts in cell A2.

Function RndHole()
  Dim Arr As Variant
  Randomize
  Arr = Range("A2", Cells(Rows.Count, "A").End(xlUp))
  RndHole = Arr(Int(UBound(Arr) * Rnd + 1), 1)
End Function

 

[NZAS]

I am not sure what you have in mind for your UserForm, so I am giving you a function that you can call in an Excel cell or from within your own VBA code. The function assumes the values are in Column A and the list starts in cell A2.

Function RndHole()
  Dim Arr As Variant
  Randomize
  Arr = Range("A2", Cells(Rows.Count, "A").End(xlUp))
  RndHole = Arr(Int(UBound(Arr) * Rnd + 1), 1)
End Function

I have a user form like user form like below with a button to action the draw random number (your Code) and have that number displayed with in the user form

 

[Rick Rothstein]

What are you using for showing the number... a ListBox, a TextBox, something else?

 

[NZAS]

The numbers are listed in column A from A4

 

[NZAS]

It would be a text box. As it would only display the random number as drawn