Button to Generate Random Cell from List

[aeseya]

Hello,

I'm trying to place a button which will randomly choose a cell from a table column and populate a target cell with its contents (or some similar way to 'show' the result). I don't have a lot of experience with excel and I've never used buttons before so I'm a bit out of my depth.

If relevant, I am using excel 365 on windows, but once it's set up I would be using the button on the excel ios app. The table is populated with text rather than numbers.

Any help would be much appreciated!

 

[aeseya]

I've made some progress on my own so updating the request.

I've used this formula to return a random cell in column C, excluding blank cells. No idea if it's the most efficient method but it seems to do the job: =INDEX(C:C,AGGREGATE(15,6,ROW(C11:C2505)/(C11:C2505<>""),RANDBETWEEN(1,COUNTA(C11:C2505))))

I now just need a button to get the formulae to run whenever I press it (open to suggestions for alternatives) but I have no clue how to go about this.

 

[kvsrinivasamurthy]

Button cannot be used for formula. Use VBA code below.
code:

Sub SelectRandomCell()
Dim Ip As Range
Dim X&, Lr&, Y&
Lr = Range("C" & Rows.Count).End(xlUp).Row
Line1:
X = WorksheetFunction.RandBetween(1, Lr)
Y = Y + 1
If Range("C" & X) <> "" And Y < 10000 Then Range("C" & X).Select Else GoTo Line1
End Sub

How to use macro code

To paste the code
In the developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the VB window.
Save file as .xlsm

To insert Button.
Select Developer in ribbon
Insert-->Form Controls--> Select First Button.
Write the button on the sheet to the required size. Macro Dialogue Opens.
Select the macro SelectRandomCell --> OK
Edit the Button Caption with the help of Right click on the button as "SELECT CELL".

 

[aeseya]

Button cannot be used for formula. Use VBA code below.
code:

Sub SelectRandomCell()
Dim Ip As Range
Dim X&, Lr&, Y&
Lr = Range("C" & Rows.Count).End(xlUp).Row
Line1:
X = WorksheetFunction.RandBetween(1, Lr)
Y = Y + 1
If Range("C" & X) <> "" And Y < 10000 Then Range("C" & X).Select Else GoTo Line1
End Sub

How to use macro code

To paste the code
In the developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the VB window.
Save file as .xlsm

To insert Button.
Select Developer in ribbon
Insert-->Form Controls--> Select First Button.
Write the button on the sheet to the required size. Macro Dialogue Opens.
Select the macro SelectRandomCell --> OK
Edit the Button Caption with the help of Right click on the button as "SELECT CELL".

Amazing! Thank you for such a detailed response.

It works perfectly to select a random cell in the list, but I was hoping to populate a target cell instead. I'm planning on using this on my phone so I'd like to avoid scrolling through the list.

Is there any relatively easy way to do that?

 

[kvsrinivasamurthy]

I didn't follow what you mean target cell.

 

[aeseya]

I didn't follow what you mean target cell.

Sorry for being unclear. I'm hoping to click the button and have the contents of a random cell in the list populate another cell in the worksheet.

e.g. 'Emma' is randomly selected from the list and 'Emma' appears in the cell next to the button.

 

[kvsrinivasamurthy]

I have changed code . Target cell is selected as D5. D5 can be changed in the code to any required desired cell.

Sub SelectRandomCell()
Dim Ip As Range, tgt As Range
Dim X&, Lr&, Y&
Set tgt = Range("D5")  'qsetting the target cell as D5
Lr = Range("C" & Rows.Count).End(xlUp).Row
Line1:
X = WorksheetFunction.RandBetween(1, Lr)
Y = Y + 1
If Range("C" & X) <> "" And Y < 1000 Then tgt = Range("C" & X) Else GoTo Line1

End Sub

 

[kvsrinivasamurthy]

You can use this improved code. Target cell is selected as D5. D5 can be changed in the code to any required desired cell.

Sub SelectRandomCell()
Dim tgt As Range
Dim X&, Lr&
Dim M
Set tgt = Range("D5")  'setting the target cell as D5
Lr = Range("C" & Rows.Count).End(xlUp).Row
M = Filter(Evaluate("transpose(if(Sheet1!C1:C" & Lr & "="""",False,row(Sheet1!C1:C" & Lr & ")))"), False, False)
X = WorksheetFunction.RandBetween(0, UBound(M))
tgt = Range("C" & M(X))
End Sub

 

[Georgiboy]

I'm planning on using this on my phone

Do macros function on the mobile version of Excel?

To the best of my knowledge, they do not.

 

[aeseya]

You can use this improved code. Target cell is selected as D5. D5 can be changed in the code to any required desired cell.

Sub SelectRandomCell()
Dim tgt As Range
Dim X&, Lr&
Dim M
Set tgt = Range("D5")  'setting the target cell as D5
Lr = Range("C" & Rows.Count).End(xlUp).Row
M = Filter(Evaluate("transpose(if(Sheet1!C1:C" & Lr & "="""",False,row(Sheet1!C1:C" & Lr & ")))"), False, False)
X = WorksheetFunction.RandBetween(0, UBound(M))
tgt = Range("C" & M(X))
End Sub

Works perfectly! Thank you so much.

Do macros function on the mobile version of Excel?

To the best of my knowledge, they do not.

Unfortunately, you're right. On my phone I'm stuck with changing the value of a random cell to get the INDEX formulae to run. If anyone has any suggestions for a quicker way to do this I'm all ears, but for now it's much better than before.