# Draw a random number from list of values

#### NZAS

##### Board Regular
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

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### lrobbo314

##### Well-known Member
Book1
ABC
1HolesRandom Pick
213
37
415
53
66
Sheet1
Cell Formulas
RangeFormula
C2C2=INDEX(\$A\$2:\$A\$6,RANDBETWEEN(1,COUNTA(\$A\$2:\$A\$6)))

#### NZAS

##### Board Regular
Book1
ABC
1HolesRandom Pick
213
37
415
53
66
Sheet1
Cell Formulas
RangeFormula
C2C2=INDEX(\$A\$2:\$A\$6,RANDBETWEEN(1,COUNTA(\$A\$2:\$A\$6)))
Could this be done using VBA
Book1
ABC
1HolesRandom Pick
213
37
415
53
66
Sheet1
Cell Formulas
RangeFormula
C2C2=INDEX(\$A\$2:\$A\$6,RANDBETWEEN(1,COUNTA(\$A\$2:\$A\$6)))
Book1
ABC
1HolesRandom Pick
213
37
415
53
66
Sheet1
Cell Formulas
RangeFormula
C2C2=INDEX(\$A\$2:\$A\$6,RANDBETWEEN(1,COUNTA(\$A\$2:\$A\$6)))

#### NZAS

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

#### Rick Rothstein

##### MrExcel MVP

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.
VBA Code:
``````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

##### Board Regular
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.
VBA Code:
``````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

##### MrExcel MVP

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

#### NZAS

##### Board Regular
The numbers are listed in column A from A4

#### NZAS

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

Replies
6
Views
103
Replies
3
Views
288
Replies
8
Views
642
Replies
0
Views
144
Replies
5
Views
457

1,127,573
Messages
5,625,591
Members
416,120
Latest member
B3nj1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back