Prize Draw with Macro

[sacen]

Hi Guys

I'm trying to get a random prize draw working with in excel. My worksheet structure as follows.

A1 person's name
B2 points obtain by the person.

A1 B2
John 10 points
Ken 20 points
Amy 30 points

1st draw. If random number result between 1 and 10 display John wins then delete John's entries.
2nd draw etc... and delete anyone that has already won a prize.


With the following code I found on this forum it partly works. It only randomly select a cell but missing displaying the winner and removing it afterwards. Any help will be appreciated.

Sub random()

Dim rng As Range

Set rng = Range("a1:a180")

rng.Cells(Int(Rnd * rng.Cells.Count) + 1).Select


End Sub

[JOHN H. DAVIS]

Please use code tags when posting. See if you can use this?

[sacen]

Hi John

Thanks, sorry I'm a novice in macros. How dose your file work I don't see any macros scripts on it ?

[JOHN H. DAVIS]

Sorry, I have 2003. You 2013. You probably lost the code on conversion. Here is the code.

Please Login or Register  to view this content.

[sacen]

Thanks John.

Is it possible to disable generating the random number at the start ? I have a fixed number of entries eg:

John: 5 entries
Mark: 10 entries
Amy: 20 entries

So if the randomly selected number between 1-5 winner is John or if selected number between 6-10 Winner is Mark and if between 11-20 Amy is the winner and so on but it will only select 5 winners from the list.

I have attached my work file to show what I meant. Do I make sense?

Attached Files:

sacen.xlsm