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!

 

[Flyboy8905]

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

This is fantastic! I was able to use this to create three different buttons on Sheet 1 to pull random data from either sheet 2, 3, or 4 by just changing the D5 range slightly for each new button as well as where it is pulling data from like this.
Quick question though, is there a simple way to add something to have it fully go through all the answers before repeating itself? I seem to get repeating answers often. I eventually do get all the answers, but sometimes it takes alot of clicks. (Im trying to make a list for my wife, and would like it to go through the whole thing before repeating if possible. )

This is basically how i changed it for my second button to pull data from sheet 3

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

 

[kvsrinivasamurthy]

Sorry. I am out of station. Please wait 2-3 days for my reply.

 

[kvsrinivasamurthy]

Try

Sub SelectRandomCell()
Dim tgt As Range
Dim T&, 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)

For T = 0 To UBound(M)
tgt.Offset(T, 0) = Range("C" & M(T))
Next T
End Sub

 

[Flyboy8905]

Try

Sub SelectRandomCell()
Dim tgt As Range
Dim T&, 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)

For T = 0 To UBound(M)
tgt.Offset(T, 0) = Range("C" & M(T))
Next T
End Sub

That seemed to just display everything in the list from the other page all at once rather than go through them all one by one. I could easily be wrong though. It just didn't seem to work for me. I don't want to take up too much of your time though. I really appreciate the other code. It helped out a lot!