Random Number Set Generator & Recorder

Hi

Hope this makes sense?

I’m using =INDEX($A$1:$J$1,RANK(A2,$A$2:$J$2)) to generate a random set of numbers in cells A4:E4

Looking for a macro that can continually generate random sets of numbers until the set in cells A4:E4 match the number set in cells F8:J8 (number order not import), then record in cell L8 how many sets (ie 1325) of numbers were generated from the beginning until a match was made.

Then continue generating random sets of numbers until a match (A4:E4) is made to the set of numbers in cells F9:J9 (number order not important), recording in cell L9 how many sets of numbers (ie 1644) it took for the match to happen AFTER the numbers in cells F8:J8 were matched. (ie 1325 sets of numbers were generated until the number set in cells F8:J8 was matched. Then it took another 1644 sets of numbers to be generated until the numbers in cells F9:J9 were matched)

Then continue generating random sets until a match (A4:E4) is made to the set of numbers in cell F10:J10, recording in cell L10 how many sets of numbers (ie 1644) it took to match AFTER the numbers in cells F9:J9 were matched.

If the recorded amount in cell L9 & L10 match (ie 1644) then generate that many more sets (1644) of numbers and record the numbers of the final set in cells F12:J12 and the value of cell L10 into cell L12.
Then reset the recorded values in cell L8:L10 and start process over recording next match values into cells F13:J13 and L13 and so forth.

Alternatively, once the number set in cells F10:J10 has been matched, if the recorded value in cell L9 & L10 don’t match, reset L8:L10 and have the process start over.

Hi shg

Thanks for the info but my post wasn't really a question. The macro I'm looking for is part of larger project that I'm working on. I only used a search criteria of 5/10 to make it easier for testing. Once I have a working macro those numbers will be increased much larger which in turn will make getting results much longer, how much longer I don't know, but that's part of what I'm working on. Thanks again

Hi shg

Thanks again, but what I'm looking for is a macro that can do what I've asked. I know what you are suggesting is more efficient but I have my reasons why I want it the other way.

Hi shg

np, tnx again for your alternative suggestion

Hi 6StringJazzer

Yes, the process could go on forever, the process doesn't end until I stop the macro from running so I could end up with 1 - 10 - 100 results depending on how long I let it run for. If I use a large set of numbers such as 15/30 like shg suggested, I may have to let the macro run for several days if not weeks until I get the results I'm looking for. With a smaller set of number like 5/10 I could get results within a few days so I wouldn't need to let it run for so long.

The way you describe it, it keeps overwriting the results from each trial. Exactly as described, you will never see any results, until you stop the process, then you will only see the last trial. How do you want to capture those results? It sounds like you also need some kind of STOP button.

Hi 6StringJazzer

Yes, the values in L8:L10 are overwritten if there is a match or no match between L9:L10.

Results will be captured in cells F12:L12, F13:L13, F14:L14 and on and on. These results don't get reset or over written.

If there is a match between L9:L10 then that value is used to generate that many more random sets and the final set is recorded in cells F12:J12 and the match value is recorded in cell L12

When another match happens, then the match value between L9:L10 is used to generate that many more random sets and the final set is recorded in cells F13:J13 and the match value is recorded in cell L13 and so on.

A stop button would work or a counter that once X number of matches happen the macro stops? Can this be done??

Hi 6StringJazzer

I ran it manually a ton of times and when L9 & L10 did match, I got a "Run-time error 438, object doesn't support this property or method" ?

On what line of code?

Originally Posted by ssjagger

Hi 6StringJazzer

I ran it manually a ton of times and when L9 & L10 did match, I got a "Run-time error 438, object doesn't support this property or method" ?

I discovered the problem. Remove the "s" from Values the two times it appears.

Please Login or Register  to view this content.

Hi 6StringJazzer

I did the edit as you suggested and it now works.

I ran it manually again until I had a match in L9 & L10 and got results recorded in F12:J12 & L12, but when I started running it again the results were erased? Is it possible for those results to stay and then the results from the next match to be recorded in F13:J13 & L13 and so on?