randomizer
 

My code shown below only works if the list in column A is odd.
If it's even, it loops continuously. Can someone help?

=======================================

Set rng = Range("mylist")
Set rng1 = rng.Offset(0, 1)
Set rng2 = rng1.Offset(0, 1)
rng2.Formula = "=rand()"
Do While Range("e19") = False
rng1.Resize(, 3).Sort Key1:=rng2
Application.Wait Now + TimeValue("00:00:01")
Loop
rng2.Clear

========================================

This is a "hat drawing" exercise. Basically, a list of names is in
column A. I have a sub to size the range 'mylist' accordingly. They
get copied to column B then randomized. The do while statement is a
check (by formulas on the spreadsheet) to see if someone "drew" their
own name. If so, the cell value E19 is set to TRUE. It runs the
randomizer again until the value in E19=False.
The problem is that it seems to loop forever if there is an even number
of names in the original list.
I can forward the entire workbook to someone if necessary, but just
wanted to find out if someone could see anything blatant.

Thanks,
Rob

randomizer
 

Well, you got it for me. My formula was referencing the cells b1:b17.
I needed a formula to reference the range offset.
Thanks...
BTW, I've been working with Excel and VBA for a long time and usually
when I have a problem, I come to this group. I have gotten extensive
help from your posts. It's gotten so I even search for your name when
looking for answers. I'd just like to say thanks for all those times
I've used your help without you knowing.

Rob


Tom Ogilvy wrote:
or use a formula like

=SUMPRODUCT(--(myList=OFFSET(myList,0,1)))=0

in E19

--
Regards,
Tom Ogilvy



"OKROB" wrote:

My code shown below only works if the list in column A is odd.
If it's even, it loops continuously. Can someone help?

=======================================

Set rng = Range("mylist")
Set rng1 = rng.Offset(0, 1)
Set rng2 = rng1.Offset(0, 1)
rng2.Formula = "=rand()"
Do While Range("e19") = False
rng1.Resize(, 3).Sort Key1:=rng2
Application.Wait Now + TimeValue("00:00:01")
Loop
rng2.Clear

========================================

This is a "hat drawing" exercise. Basically, a list of names is in
column A. I have a sub to size the range 'mylist' accordingly. They
get copied to column B then randomized. The do while statement is a
check (by formulas on the spreadsheet) to see if someone "drew" their
own name. If so, the cell value E19 is set to TRUE. It runs the
randomizer again until the value in E19=False.
The problem is that it seems to loop forever if there is an even number
of names in the original list.
I can forward the entire workbook to someone if necessary, but just
wanted to find out if someone could see anything blatant.

Thanks,
Rob

randomizer
 

Rob,
Thank you for your kind words!

--
Regards,
Tom Ogilvy


"OKROB" wrote in message
oups.com...
Well, you got it for me. My formula was referencing the cells b1:b17.
I needed a formula to reference the range offset.
Thanks...
BTW, I've been working with Excel and VBA for a long time and usually
when I have a problem, I come to this group. I have gotten extensive
help from your posts. It's gotten so I even search for your name when
looking for answers. I'd just like to say thanks for all those times
I've used your help without you knowing.

Rob


Tom Ogilvy wrote:
or use a formula like

=SUMPRODUCT(--(myList=OFFSET(myList,0,1)))=0

in E19

--
Regards,
Tom Ogilvy



"OKROB" wrote:

My code shown below only works if the list in column A is odd.
If it's even, it loops continuously. Can someone help?

=======================================

Set rng = Range("mylist")
Set rng1 = rng.Offset(0, 1)
Set rng2 = rng1.Offset(0, 1)
rng2.Formula = "=rand()"
Do While Range("e19") = False
rng1.Resize(, 3).Sort Key1:=rng2
Application.Wait Now + TimeValue("00:00:01")
Loop
rng2.Clear

========================================

This is a "hat drawing" exercise. Basically, a list of names is in
column A. I have a sub to size the range 'mylist' accordingly. They
get copied to column B then randomized. The do while statement is a
check (by formulas on the spreadsheet) to see if someone "drew" their
own name. If so, the cell value E19 is set to TRUE. It runs the
randomizer again until the value in E19=False.
The problem is that it seems to loop forever if there is an even number
of names in the original list.
I can forward the entire workbook to someone if necessary, but just
wanted to find out if someone could see anything blatant.

Thanks,
Rob