how to use cell value to define range/variable in vba

dominicb posted this as an example of generating unique random numbers from a defined range 1-90
so i thought i'd modify it fot use in a raffle
code was

Please Login or Register  to view this content.

so after a bit of playing i thought
if i changed this to

Please Login or Register  to view this content.

i could use it for raffle tickets, it seems to work ok
but then i thought what if i only sell say tickets 200 to 599
and only those to be selected, this is where i cant figure it out. ideally i'd like to be able to put start and end numbers of range in say a2/b2.
and..................sorry.....a means of stopping it cleanly when all prizes given out. i know control brk works but is there another option?

Here's an array function you can use to generate a random, non-repeating sequence from 1 to the number of cells in which the array formula appears:

Please Login or Register  to view this content.

If you want numbers from 200 to 599, then array-enter this into a 400-cell range:

=RandSeq() + 199

martindwilson,

My mistake.


Have a great day,
Stan

ps shhg tyhanks for th code nut!!!!!!!!!!!
ok i took the dominicb one and reworked it like this

Please Login or Register  to view this content.

now what does the Dim rand1(2000) bit do?
in the original it matched range of numbers but i found if i went too low say 10 it repeated so i arbitrarily put 2000 and it works fine
i tried replacing it with "d" but it wont let me put a variable in there
edit it doesnt work forsplit range sat 301-308 it returns 1thro 8