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)
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).
JMB17 Hi again. I got it to work well when the number of buyers was equally divisable by 24. But when it was an odd number such as below I had issues.
I resized the table to 207 sweeps buyers and followed your instructions but for some reason there are some gaps from Sweeps buyers 193 - 207. These final rows remained as false.
Perhaps to make this work, I will need to sell the number of sweeps tickets that will make it divisable by 24 exactly since I am trying to replicate what happens in the real world of 'sweeps'.
You have been a terrific help to me in this so thanks. When we get closer to the date I will come back to you with how I am going with the number of sales. I have put a deadline of 12 noon on Mon 2nd Nov for me to finalise allocations then I am going to send 24 emails to the buyers advising them of their horse. The race itself takes place the following day on Tues 3rd Nov. Fun hey?