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)
I think you can do that with a circular reference and with iterative calculation turned on (File/Options/Formulas - Enable iterative calculation - I set mine for 200).
To generate a new list, you can change the "Reset" drop down to TRUE, then hold down the F9 (calculate) key for a few seconds to randomize the list. Then, change the "Reset" to FALSE and hit/hold the F9 key until each horse appears 5 times (the "Condition Met" cell should change to TRUE).
If it were me, once the list is generated, I would copy/paste special value the list to another worksheet just to ensure I didn't accidentally trigger the formulas to recalculate later and cause the list to be re-shuffled.
I am sorry to bother you again but can you tell me how to do the second part
ie. Reset dropdown. I can't locate it.
It might be easier if you could make the change in the file that I sent. And I agree moving the final horse list to a new worksheet is what I would do also.
Regards
- If you click on cell B1, you should see a drop down button with True/False options.
JMB17 Hi Again, you are so clever. I would never have been able to work that out. I got the spreadsheet to output 5 horses for the selected buyers. So if I end up with say 300 buyers do I have to change the settings in formulas. You had put a setting at 200. Will I need to change that depending on the number of buyers I end up with ?
Is there anything else I should know. To ensure I don't mess up your formulas, I will just amend your spreadsheet and copy the buyer names to column A. If then I just update the Horse List values with the actual horse name, once I know it, will that impact the formulas. I am hoping not since it is referencing a cell.
It seems so much easier to cut up the names and put them in a hat. We would be doing that if we weren't living with a pandemic !! Thankyou so much for your help. You are amazing.
Sorry it took me a little while to get back to you.
I modified the workbook to use structured tables, which will make the lists easier to resize (Table Tools tab, Resize) and add more entries.
Also, I changed the formulas to use structured table references, so you should not have to change the formulas after resizing and changing your customer/horse lists.
I added a workbook open macro to turn on iterative calculation to save you a step (just don't save the workbook as an xlsx file as that will strip out the macros).