Non-repeating list of random numbers between 0001 and 9999, only need 2051 of the 9999

[robbertly]

Hi, I want to generate a list of non-repeating random numbers between 0001 and 9999, but I only need a total of 2051 of the 9999 I want to have 5 separate / individual columns, each with one set of non-repeating numbers. Any suggestions, comments, ideas welcome. Thanks

 

[Rick Rothstein]

This should do what you want...

[table="width: 500"]
[tr]
	[td]Sub TenColumnsOf2051UniqueRandomNumbersAcrossTenSheets()
  Dim C As Long, Cnt As Long, Rw As Long, ShtNum As Long, RandIndx As Long, Result(1 To 2051, 1 To 10) As Long
  Dim Tmp As Variant, Nums As Variant
  Application.ScreenUpdating = False
  Randomize
  For ShtNum = 1 To 10
    Erase Result
    For C = 1 To 10
      Nums = [ROW(1:9999)]
      Rw = 0
      For Cnt = 9999 To 7949 Step -1
        RandIndx = 1 + Int(Rnd() * Cnt)
        Tmp = Nums(RandIndx, 1)
        Nums(RandIndx, 1) = Nums(Cnt, 1)
        Rw = Rw + 1
        Result(Rw, C) = Tmp
      Next
    Next
    Sheets(ShtNum).Range("E9").Resize(2051, 10) = Result
  Next
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[robbertly]

Hi Rick,

Again, many thanks for the prompt reply and the macro.

Apologies for the delay in getting back.

Yes, it works perfectly and does exactly as I need.

Many thanks for your assistance with this, it is not something I could have achieved without your expert design and creation.

You have saved me endless hours of mindless and repetitive copy and paste.... what a relief!

Just on minor thing of note, when I run the macro I get a message - "Subscript out of range" - however, its still carries out the instruction and creates the 10 columns with the numbers.

I think this might be down to something I did in installing/setting up the macro rather than the content of it.

Once again, a very big thank you for this.

Thanks,

Robert

 

[Rick Rothstein]

Just on minor thing of note, when I run the macro I get a message - "Subscript out of range" - however, its still carries out the instruction and creates the 10 columns with the numbers.

Off the top of my head, I don't see anything in my code that should produce that error message. Make sure you are using the latest code I posted. I noticed you were online when I posted a slight modification to my code to insure better randomization and wonder if you might have downloaded the older code while I was updating the post. I don't think my older code could have produced that error either, but I did not scrutinize it to see.

Out of curiosity, when does that error message occur... at the end of the macro's run? Does the code physically stop when that error message is displayed? If so, what line of code is highlighted?

 

[Peter_SSs]

We can make one more modification... probably does not speed things up noticeably, but the red highlighted line of code is no longer needed with your modifications and can be deleted.

No, we can't do that - at least not on its own. If you did that then in columns after the first ..
- Duplicates will occur
- In each column there will be numbers from the 1-9999 range that are impossible to get in that column

BTW, no need to Erase Result in your latest code as every element of the array gets recalculated/overwritten for each sheet.

 

[robbertly]

Hello Rick,

Apologies for the delay in responding.

Just to confirm the issue was 'human error', namely mine!

In testing the macro on a workbook I only opened/set up one worksheet in error. As the macro was designed for 10 pre-designed worksheets it could not complete it task properly, hence the errors.

When I realized my mistake, I set up the 10 worksheets in the workbook and the macro worked absolutely perfectly.

Thank you for your assistance, support and patience with this it is a reflection of your expertise.

You have saved me a lot of time, energy and stress by virtue of the benefit of your expertise.

Much appreciated, and again, please keep up the good for other excel users.

Regards,

Robert

 

[robbertly]

Hello Peter,

Thanks for following up.

As mentioned in my reply to Rick, the problem was 'human error' - mine!

When I ran the macro as I should have it works perfectly.

Many thanks for the follow up and feedback.

This is an excellent forum and community - keep up the good work.

Regards,

Robert

 

[Peter_SSs]

Hello Peter,

Thanks for following up.

No problem - glad to contribute.