Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
repeating values | Excel Worksheet Functions | |||
repeating values | Excel Worksheet Functions | |||
can i stop repeating values? | Excel Discussion (Misc queries) | |||
excluding repeating values | Excel Discussion (Misc queries) | |||
Repeating Values | Excel Programming |