Detect/find Duplicate Values In Code

Hello FRIEL,

An easier method would be to check each number as it is generated against an array. Each element of the array corresponds directly to the number generated. If the array element is zero then that number hasn't been generated.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Hello FRIEL,

The underscore character _ signals VBA that the line continues.

The 99999 for the array is used because you are generating 5 digit decimal numbers. The lowest random number would be 0 and the highest 99999. Each one is saved into a unique memory location. The location (element address) equals the random number. Once a number is generated, a 1 is stored in that location to indicate that number has now been used. The code will look at this flag and generate another random number if it is not zero.

Example
The number 12345 is generated. The code looks at that array element in RandNums: RandNums(12345). If this number has not been generated before, RandNums(12345) will be 0. Now that the number has been generated, the location is flagged with a 1 to indicate this is no longer a unique random number.

Sincerely,
Leith Ross