REpeating Values
 

Hello,

I have some code that generates Random draw winners, but if a name is
repeated in the list I want to have a pop-up identifing this and ask me if I
want the name changed or kept the same, this is just in case there is only 1
entry with 3 prizes.

I have placed the code below (I know it is messy but this is the only way I
know how to write code)

Please help

Application.Goto Reference:="Random_Number_Generator"
ActiveCell.FormulaR1C1 = "=ROUND((RAND()*(1-" & ValueMAM & ")+" &
ValueMAM & "),0)"
Application.Goto Reference:="MAM_Winner_1"
ActiveCell.FormulaR1C1 =
"=vlookup(Random_Number_Generator,MAMDraw!A:B,2,0) "
SendKeys "{f2}" & "{Enter}", True
Application.Goto Reference:="MAM_Winner_1"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.Goto Reference:="MAM_Winner_2"
ActiveCell.FormulaR1C1 =
"=vlookup(Random_Number_Generator,MAMDraw!A:B,2,0) "
SendKeys "{f2}" & "{Enter}", True
Application.Goto Reference:="MAM_Winner_2"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.Goto Reference:="MAM_Winner_3"
ActiveCell.FormulaR1C1 =
"=vlookup(Random_Number_Generator,MAMDraw!A:B,2,0) "
SendKeys "{f2}" & "{Enter}", True
Application.Goto Reference:="MAM_Winner_3"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

REpeating Values
 

the basic approach would be:

After you make the entry, then check if it is a duplicate:

if Application.Countif(Range("A1:A50","John")) 2 then


In corporate something like that in your code.

--
Regards,
Tom Ogilvy



"GBH99" wrote:

Hello,

I have some code that generates Random draw winners, but if a name is
repeated in the list I want to have a pop-up identifing this and ask me if I
want the name changed or kept the same, this is just in case there is only 1
entry with 3 prizes.

I have placed the code below (I know it is messy but this is the only way I
know how to write code)

Please help

Application.Goto Reference:="Random_Number_Generator"
ActiveCell.FormulaR1C1 = "=ROUND((RAND()*(1-" & ValueMAM & ")+" &
ValueMAM & "),0)"
Application.Goto Reference:="MAM_Winner_1"
ActiveCell.FormulaR1C1 =
"=vlookup(Random_Number_Generator,MAMDraw!A:B,2,0) "
SendKeys "{f2}" & "{Enter}", True
Application.Goto Reference:="MAM_Winner_1"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.Goto Reference:="MAM_Winner_2"
ActiveCell.FormulaR1C1 =
"=vlookup(Random_Number_Generator,MAMDraw!A:B,2,0) "
SendKeys "{f2}" & "{Enter}", True
Application.Goto Reference:="MAM_Winner_2"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.Goto Reference:="MAM_Winner_3"
ActiveCell.FormulaR1C1 =
"=vlookup(Random_Number_Generator,MAMDraw!A:B,2,0) "
SendKeys "{f2}" & "{Enter}", True
Application.Goto Reference:="MAM_Winner_3"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

REpeating Values
 

Hi Tom,

I have tried this and when I added the MSG box in I add a select case
option. Should I be using something else? I have tried
if Application.Countif(Range("A1:A50","John")) 2 then
MSGBOX DUPERR("Duplicate, Replace?",VBYesNo,"Replace")
Select Case DUPERR
Case 6
ActiveCell.clearcontents
'then I have put the code to create the cell entry again.
Case 7
MSGBOX "Continuing"

"Tom Ogilvy" wrote:

the basic approach would be:

After you make the entry, then check if it is a duplicate:

if Application.Countif(Range("A1:A50","John")) 2 then


In corporate something like that in your code.

--
Regards,
Tom Ogilvy



"GBH99" wrote:

Hello,

I have some code that generates Random draw winners, but if a name is
repeated in the list I want to have a pop-up identifing this and ask me if I
want the name changed or kept the same, this is just in case there is only 1
entry with 3 prizes.

I have placed the code below (I know it is messy but this is the only way I
know how to write code)

Please help

Application.Goto Reference:="Random_Number_Generator"
ActiveCell.FormulaR1C1 = "=ROUND((RAND()*(1-" & ValueMAM & ")+" &
ValueMAM & "),0)"
Application.Goto Reference:="MAM_Winner_1"
ActiveCell.FormulaR1C1 =
"=vlookup(Random_Number_Generator,MAMDraw!A:B,2,0) "
SendKeys "{f2}" & "{Enter}", True
Application.Goto Reference:="MAM_Winner_1"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.Goto Reference:="MAM_Winner_2"
ActiveCell.FormulaR1C1 =
"=vlookup(Random_Number_Generator,MAMDraw!A:B,2,0) "
SendKeys "{f2}" & "{Enter}", True
Application.Goto Reference:="MAM_Winner_2"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.Goto Reference:="MAM_Winner_3"
ActiveCell.FormulaR1C1 =
"=vlookup(Random_Number_Generator,MAMDraw!A:B,2,0) "
SendKeys "{f2}" & "{Enter}", True
Application.Goto Reference:="MAM_Winner_3"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False