Is there any macro to randomize the "Name" into "The Winner" column, without duplicate?
I would like it to start from very bottom to the $10000.
Maybe no need for the macro...
Actually, the PIC will call out the "Name" physically by lucky draw paper box, then I have to manually input their "Name" into "The Winner" column.
Any method to make thing easier here?
Thank you!
Lucky Draw.xlsm
One way.
Click "NEXT button to generate random winner
"CLEAR" to start from beginning
Column B should not be there, just for testing
PHP Code:Option Explicit
Sub Lucky()
Dim lr&, i&, j&, k&, r&
Dim win As Range, rng, res(1 To 150, 1 To 1)
Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
Randomize
lr = Cells(Rows.Count, "A").End(xlUp).Row
rng = Range("A2:A" & lr).Value
For i = lr To 2 Step -1
If Cells(i, "F") = "" Then
Set win = Range(Cells(i + 1, "F"), Cells(lr + 1, "F"))
For j = 1 To UBound(rng)
If Application.CountIf(win, rng(j, 1)) = 0 Then
k = k + 1: res(k, 1) = rng(j, 1)
End If
Next
Exit For
End If
Next
If k = 0 Then Exit Sub
r = Rnd() * k + 1
Cells(i, "F") = res(r, 1)
End Sub
Sub CLEAR()
Dim inf
inf = MsgBox("All existing data will be cleared! Do you want to continue", vbYesNo)
If inf = vbNo Then Exit Sub
Range("F2:F1000").ClearContents
End Sub
Quang PT
Your first requestcan be handled with the following :Is there any macro to randomize the "Name" into "The Winner" column, without duplicate?
In the above project you would pre-enter the contestant names in Col A. Click the PICK RANDOM button and the macro randomly selects one contestant and places a mark in Col C to note that name has already been chosen.
Please Login or Register to view this content.
The macro continues until all names have been selected. You can decide at any time to clear Col C by clicking the CLEAR RANGE button.
Download file below : Random Pick 1 by 1.xlsm
####################################################################################
Your second requestcan be fulfilled with the following :Actually, the PIC will call out the "Name" physically by lucky draw paper box, then I have to manually input their "Name" into "The Winner" column.
You can edit the macro code to check any column desired. For example, maybe you only desire to enter the participant names in Col A ... edit "Set myrange = Range("B1:B10")" to say "Set myrange = Range("A1:A10")".Please Login or Register to view this content.
The range may also be extended as far down a column as you desire. For example you could cover the range "A1:A200" to include a possible 200 participants.
Download file below : Duplicate Check and Highlight.xlsm
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks