Forum Discussion
Random selection for raffle winners
- With your permission, if I can recommend you, add a file (without sensitive data) to your project.
Explain your plans in relation to this file.
So you can get a solution that is tailored to your needs much faster.
At the same time, it is much easier for someone who wants to help to understand the subject.
A win-win situation for everyone.
Here is a VBA suggestion.
The list of names in column A can be of any length, but must begin in A1.
The second macro builds in the button to trigger the first macro.
Code:
Sub raffle ()
Dim rngBer As Range
Dim lngZ As Long
Randomize
lngZ = Cells (Rows.Count, 1) .End (xlUp) .Row
Set rngBer = Range (Cells (1, 1), Cells (lngZ, 1))
lngZ = CLng (Int (lngZ * Rnd + 1))
Cells (lngZ, 1) .Offset (0, 1) .Value = Cells (lngZ, 1) .Offset (0, 1) .Value + 1
End Sub
'************************************************* *
Sub button ()
With ActiveSheet.Buttons.Add (243, 24, 104.25, 45)
.OnAction = "Raffle"
.Characters.Text = "Start"
End With
End Sub
If you click the button with the right mouse button, you can move it to any position.I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
The file contains circular references by design (otherwise, the random function would never "lock" and just keep recalculating). In order for the formulas to calculate, formula iteration must be enabled (File/Options/Formulas - Enable Iterative Calculation.
When the option is not enabled, you will get the circular reference error. The workbook contains an auto open macro to enable iterative calculation, but it doesn't run before excel attempts to calculate the formulas - so you may get the circular reference error before the auto open macro enables iterative calculation (but should be able to just click through it).
If it doesn't appear to be calculating, double check that option is enabled.
JMB17 Many thanks for explanation. I have learnt alot during this but I shall leave such work to experts like you. Great job and thanks for being so responsive. I will give you an update when my event is imminent. You may even want to buy a sweeps ticket here https://www.trybooking.com/events/landing?eid=667369&
Cheers !
Maree