+ Reply to Thread
Results 1 to 6 of 6

[VBA] Create a random unique numbers generator without functions

  1. #1
    Forum Contributor
    Join Date
    04-13-2017
    Location
    Buchares
    MS-Off Ver
    2016
    Posts
    117

    [VBA] Create a random unique numbers generator without functions

    Hey guys, I want to create a sub that gives me random numbers from a specific path

    I have some data store on a column (C) and i will extract it on column A


    Please Login or Register  to view this content.

    The problem is that the code goes out of memory and doesn't offer me the unique number.

    Thanks ,
    Alex

  2. #2
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: [VBA] Create a random unique numbers generator without functions

    You don't run out of memory as such - you run out of stack space (OK,. so it's all memory but the stack is only 64Kb)...

    Calling a procedure recursively like
    Please Login or Register  to view this content.
    can fill the stack (for a simple call with no local variables) in about 6000 iterations. You have a few variables in your procedure so they all have to be saved before each call reducing the number of recursions.

    What is the purpose of the procedure?

  3. #3
    Forum Contributor
    Join Date
    04-13-2017
    Location
    Buchares
    MS-Off Ver
    2016
    Posts
    117

    Re: [VBA] Create a random unique numbers generator without functions

    well that's the first part of the code . later on i will have to extract another data from the rows that the number was extracted and compare it with another data stored in another cell. I want to create unique assignments from a list

  4. #4
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: [VBA] Create a random unique numbers generator without functions

    OK, what about you explain the logic of the code...

    Going back to the number of iterations, you use 5 or so variables in your procedure - running a test procedure with 5 variables of type Variant caused an Out of Stack Space error in 4300 iterations compared to the 6000+ with no variables. Nothing to do with your problem just something I never tested before.
    Last edited by cytop; 04-27-2017 at 07:24 AM.

  5. #5
    Forum Contributor
    Join Date
    04-13-2017
    Location
    Buchares
    MS-Off Ver
    2016
    Posts
    117

    Re: [VBA] Create a random unique numbers generator without functions

    the logic is simple step by step :
    1) count how many rows are in column C
    2) extract a random number from (1 to step 1)
    3) count how many rows are in column A
    4) compare each cell value from column A
    5) if its unique , put the number in column A, if it's not unique , go again to step 2
    6) done

  6. #6
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: [VBA] Create a random unique numbers generator without functions

    OK
    1. No problem and your code is there
    2. No idea what you mean. Get the value of a random cell from the used cells in Col C?

    Come back to the rest after point 2 is cleared up

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to Create a Random Generator
    By Justair07 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-25-2013, 04:30 PM
  2. Random numbers generator
    By Eric Excels in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-14-2011, 08:49 AM
  3. Unique Random Number generator
    By David Obeid in forum Excel General
    Replies: 3
    Last Post: 01-28-2008, 11:27 PM
  4. [SOLVED] How do I create a random number generator in excel?
    By Katie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-19-2006, 08:55 PM
  5. Create a Graph with Random Number Generator
    By Luke Williams in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 12-19-2005, 02:45 PM
  6. Can excel be used to create a random number generator?
    By comotoman in forum Excel General
    Replies: 2
    Last Post: 09-16-2005, 04:55 PM
  7. Create Random Number Generator
    By Pascale in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-06-2005, 12:05 PM
  8. How do you create a random number generator in Excel?
    By gmrobillard in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-23-2005, 06:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1