Draw a random number from list of values

NZAS

Board Regular
Joined
Oct 18, 2012
Messages
116
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
Jul 14, 2008
Messages
3,097
Office Version
  1. 365
Platform
  1. Windows
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)))
 
Solution

NZAS

Board Regular
Joined
Oct 18, 2012
Messages
116
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
Oct 18, 2012
Messages
116
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Could this be set up with using user form and the vba
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,873
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Oct 18, 2012
Messages
116
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

1607979892315.png
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,873
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

NZAS

Board Regular
Joined
Oct 18, 2012
Messages
116
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
The numbers are listed in column A from A4
 

NZAS

Board Regular
Joined
Oct 18, 2012
Messages
116
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
It would be a text box. As it would only display the random number as drawn
 

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top