Random number generator not always generating random numbers

Hi,

I have five different work units each with different regulations and different number of regulations. Periodically, we do random inspections to endure the regulations are being followed. I created a macro to randomly generate three non-repeating numbers which correspond to the list of regulations. Each work unit has its own sheet and each sheet has its own random number generator macro. The only difference between each macro is the upperbound because the upperbound corresponds to the number of regulations in each work unit: Sheet 1 has 105 regulations, Sheet 2 has 109, Sheet 3 has 77, Sheet 4 has 71, and Sheet 5 has 74.

The macro works as desired except for when Sheet 3 (named GFCCH) is the first sheet opened and the macro for random numbers relating to this sheet is run before any macro on any other sheet. Instead of random numbers, the set of three numbers generated are predictable such that if I run the macro four times, I’ll always get: 55/42/45; 23/24/60; 2/59/63; and 55/4/32. However, if I open Sheets 1,2,4, or 5 first and run the random number generating macro on that sheet, then go back to Sheet 3 to run the random number generating macro on Sheet 3, the three numbers generated on Sheet 3 will no longer be predictable.

I have attached the workbook. Does anyone know why the results on Sheet 3 are not random ONLY if the random number generating macro on Sheet 3 is run first? I have this problem in both Excel 2016 and Excel 365 and with 32 or 64-bit installs.

I thank you in advance for any help or advice you provide.

Hi there,

I know that you've asked for an explanation rather than a workaround, but this workaround will simplify and reduce the code you need to meet your requirements. The code I've used is as follows:

Please Login or Register  to view this content.

One routine for the entire workbook rather than one routine per worksheet.

Maybe sometime I'll try to figure out why your code works the way it does, but in the meantime, happy random numbering!

Hope this helps.

Regards,

Greg M

Thank you Greg M and AlphaFrog for the quick responses!

This was a piece of code that I wrote for the five separate work units before they were merged together into one work unit. As a result, it was simpler for me to keep them as individual macros instead of figuring out how to rewrite the code to be more tight and efficient (I'm pretty much a novice to VBA programming). That is my excuse and I'm sticking to it!

As for having to seed the randomizer...would never have thought of that, AlphaFrog. This is great info to have going forward.

The knowledge of the experts on this forum continues to amaze me!

Hi again,

Many thanks for your feedback and also for the Reputation increase - much appreciated!

You're welcome - glad I was able to help.

Best regards,

Greg M