VBA Code for Random Selection

Question

Dear Experts,This is overly simple but I am an old self taught user!I have a list of names that I use to generate a random selection with the RAND function.&nbsp; The selected person display changes with every recalculation which works for my purpose.To add some drama for the audience (and who doesn't need more drama in their life!) I would like to simply recalculate the sheet 100 times (maybe more or less depending on the timing using Recalculate) and display a countdown counter in a cell, 100-1 for every recalculation to produce a spinner effect to the selection process.&nbsp; This way the audience can see their names flash with each recalculation but also see how close it is to the end of the "spin".In the old days, we used to call it a DO loop.A simple problem from my old BASIC, FORTRAN or COBOL days, but I just haven't mastered the syntax of&nbsp;VBA!I am using EXCEL Home and Student&nbsp;Microsoft® Excel® 2021 MSO (Version 2505 Build 16.0.18827.20102) 64-bitMany Thanks!

hansvogelaar · Accepted Answer

See the attached demo (I hope that the forum won't delete it). It uses random numbers in the hidden column B.
The code that the button executes is
Sub Button1_Click()
    Dim i As Long
    Dim j As Long
    ' Count down from 100 to 0
    For i = 100 To 0 Step -1
        ' Display the counter
        Range("D4").Value = i
        ' Sort the data on the hidden column B
        Range("A1").CurrentRegion.Sort Key1:=Range("B1"), Header:=xlYes
        ' Loop to slow down execution
        For j = 1 To 3000 ' Experiment to find the best value
            DoEvents
        Next j
    Next i
End Sub
You'll have to allow macros when you open the workbook.

Forum Discussion

VBA Code for Random Selection

1 Reply

Resources