I can use a formula to generate random non-repeating numbers but the formula uses volatile functions which means the numbers change at each calculation.
Does anyone have a UDF that will do this that is not volatile?
I can use a formula to generate random non-repeating numbers but the formula uses volatile functions which means the numbers change at each calculation.
Does anyone have a UDF that will do this that is not volatile?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Why a UDF and not just a macro?
Surround your VBA code with CODE tags e.g.;
[CODE]your VBA code here[/CODE]
The # button in the forum editor will apply CODE tags around your selected text.
With a UDF you would have the versatility of defining the number range.
=RANDNUMS(start_num,end_num)
=RANDNUMS(100,150)
Yeah, but a function can only write a value to the cell in which it sits.
Pete
So you want it to be Volitile with just the input numbers assuming they reference two cells? If yes, you could use the Worksheet_Change event procedure. If No, a macro could prompt for start_num,end_num. Not to beat a dead horse here, but I still don't see the need for a UDF.
Try my luck :
Usage :Please Login or Register to view this content.
This UDF is to be used as array formula.
For example,
select A1:A10, press F2, and type =RANDNUMS(1,20) , confirmed with "Ctrl Shift Enter" keys
select C1:G1, press F2, and type =RANDNUMS(1,20) , confirmed with "Ctrl Shift Enter" keys
select C4:D7, press F2, and type =RANDNUMS(1,20) , confirmed with "Ctrl Shift Enter" keys
If number of cells selected is more than available unique numbers, the UDF will return a message.
Last edited by karedog; 08-11-2016 at 12:51 PM. Reason: Fix nStart
1. I care dog
2. I am a loop maniac
3. Forum rules link : Click here
3.33. Don't forget to mark the thread as solved, this is important
Tried it in Excel 2010 and it just hangs.
Selected A1:A5 and array entered:
=RANDNUMS(1,5)
Have to kill it using Task Manager!
Sorry, I already fix the code before you posting. Please check the fixed code.
Thanks
This is what led me to post this question:
https://www.excelforum.com/showthread.php?t=1151246
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks