Macro to randomly choose numbers

GeeBDee

New Member
Joined
Dec 7, 2015
Messages
36
Hi

I would like to click on a button to have a macro run and choose any amount of random numbers between 1 - 10. So click the button and it may choose 3 random numbers between 1 - 10, click it again and it may choose 5 random numbers, then click and it chooses 9 random numbers and so on.

I would like to know if there is anyway a macro is capable of this and if so, please advise the code?

Or am I asking too much?

If this is not possible then a macro to just choose 1 random number between 1 - 10 please.

Grant
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

SpreedsheetCrusader

Board Regular
Joined
Sep 30, 2015
Messages
130
without a macro i guess this works :

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">from</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">to</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">choose</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;;">6</td><td style="text-align: right;;">2</td><td style="text-align: right;;">7</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C7</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">$D$2,$D$3</font>)*(<font color="Blue">--(<font color="Red">COLUMN(<font color="Green">C6</font>)-2</font>)<$D$4</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D7</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">$D$2,$D$3</font>)*(<font color="Blue">--(<font color="Red">COLUMN(<font color="Green">D6</font>)-2</font>)<$D$4</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E7</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">$D$2,$D$3</font>)*(<font color="Blue">--(<font color="Red">COLUMN(<font color="Green">E6</font>)-2</font>)<$D$4</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F7</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">$D$2,$D$3</font>)*(<font color="Blue">--(<font color="Red">COLUMN(<font color="Green">F6</font>)-2</font>)<$D$4</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G7</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">$D$2,$D$3</font>)*(<font color="Blue">--(<font color="Red">COLUMN(<font color="Green">G6</font>)-2</font>)<$D$4</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H7</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">$D$2,$D$3</font>)*(<font color="Blue">--(<font color="Red">COLUMN(<font color="Green">H6</font>)-2</font>)<$D$4</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I7</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">$D$2,$D$3</font>)*(<font color="Blue">--(<font color="Red">COLUMN(<font color="Green">I6</font>)-2</font>)<$D$4</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J7</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">$D$2,$D$3</font>)*(<font color="Blue">--(<font color="Red">COLUMN(<font color="Green">J6</font>)-2</font>)<$D$4</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K7</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">$D$2,$D$3</font>)*(<font color="Blue">--(<font color="Red">COLUMN(<font color="Green">K6</font>)-2</font>)<$D$4</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">L7</th><td style="text-align:left">=RANDBETWEEN(<font color="Blue">$D$2,$D$3</font>)*(<font color="Blue">--(<font color="Red">COLUMN(<font color="Green">L6</font>)-2</font>)<$D$4</font>)</td></tr></tbody></table></td></tr></table><br />

the -2 thing after the COLUMN function is just to count for columns A and B;

i formatted the cells where the numbers appears using the following custom format:

Code:
[White][=0]General;[Black]General
 

SpreedsheetCrusader

Board Regular
Joined
Sep 30, 2015
Messages
130
another take with macros on the same sheet structure i presented above:

Code:
Sub randomgen()

Range(Range("c7"), Range("c7").End(xlToRight)).Clear


Dim F As Integer: F = Range("d2").Value
Dim T As Integer: T = Range("d3").Value
Dim R As Integer: R = Range("d4").Value




For Each cell In Range(Cells(7, 3), Cells(7, R + 2))
cell.Value = Application.WorksheetFunction.RandBetween(F, T)
Next cell




End Sub
link the macro with a shape
 
Last edited:

GeeBDee

New Member
Joined
Dec 7, 2015
Messages
36
Thank you Speedsheetcrusader, that works very well. But I really would like to have it via a macro so that a button has to be clicked.

Grant
 

GeeBDee

New Member
Joined
Dec 7, 2015
Messages
36
sorry we crossed over messages, thank you and I will try this and come back soon
 

SpreedsheetCrusader

Board Regular
Joined
Sep 30, 2015
Messages
130
bro there was some error in my previous code :p

i fixed it

check it again please

Code:
Sub randomgen()

Range(Range("c7"), Range("c7").End(xlToRight)).Clear


Dim F As Integer: F = Range("d2").Value
Dim T As Integer: T = Range("d3").Value
Dim R As Integer: R = Range("d4").Value




For Each cell In Range(Cells(7, 3), Cells(7, R + 2))
cell.Value = Application.WorksheetFunction.RandBetween(F, T)
Next cell




End Sub
 

GeeBDee

New Member
Joined
Dec 7, 2015
Messages
36
Yes that macro works perfectly, thank you so much.

I do have another question?

Can I associate a picture to a number that is radomly chosen? This is for display purposes.
 

SpreedsheetCrusader

Board Regular
Joined
Sep 30, 2015
Messages
130
that is interesting.

but i am still a noob that is beyond my skills

i guess you will have to wait for the geeks, scientists, nerds , Excel gods to visit your thread and shows us how this can be done :biggrin: i am sure it is do-able, but that is beyond me.:(
 

GeeBDee

New Member
Joined
Dec 7, 2015
Messages
36
Ha ha thank you for what you have advised anyway, you have been very helpful. Maybe I will start a new thread to ask this question.

Cheers
G
 

Watch MrExcel Video

Forum statistics

Threads
1,102,678
Messages
5,488,223
Members
407,632
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top