{"id":127,"date":"2008-08-19T14:10:18","date_gmt":"2008-08-19T13:10:18","guid":{"rendered":"http:\/\/simoncpage.co.uk\/blog\/?p=127"},"modified":"2008-10-19T14:53:26","modified_gmt":"2008-10-19T13:53:26","slug":"excel-vb-unique-random-numbers","status":"publish","type":"post","link":"https:\/\/simoncpage.co.uk\/blog\/2008\/08\/excel-vb-unique-random-numbers\/","title":{"rendered":"Excel VB | unique random numbers"},"content":{"rendered":"

I often like to fill my excel model and workbooks with data to test out\u00a0functionality and what better than a random\u00a0range of numbers. (hint look in my other post about changing negative numbers\u00a0to positives and vice versa<\/a> if you want to make the them different sizes for instance if you wanted the numbers in the thousands).<\/p>\n

This code below basically starts with the first cell of the selection and creates a random\u00a0number and then check that number to any others in the\u00a0range that are\u00a0duplicates – looping until there are\u00a0no matches\u00a0and then\u00a0carrying on to the next cell in the range to create another unique random number.<\/p>\n

Sub RandomNoDuplicates()
\nDim LowerLimit As Byte
\nDim Limit2 As Long<\/p>\n

Call CheckProtectedSheet
\nCall CheckForMultipleAreas<\/p>\n

If MsgBox(“This will place a unique random number in each cell in your selection?” & vbNewLine & “(n.b. existing values will be overwritten)”, vbQuestion + vbOKCancel, AT & ” – Insert random numbers without duplicates”) = vbCancel Then Exit Sub
\nLowerLimit = 1
\nLimit2 = Selection.Cells.Count
\nCellen = Selection.Cells.Count
\ni = 1
\nSelection.ClearContents<\/p>\n

For Each rngCel In Selection
\nApplication.StatusBar = “Processing random numbers: ” & Int((i * 100) \/ Cellen) & ” %”<\/p>\n

Section1:
\nrngCel.Value = Int((Limit2 – LowerLimit + 1) * Rnd + LowerLimit)<\/p>\n

If Application.WorksheetFunction.CountIf(Selection, rngCel.Value) = 1 Then
\nGoTo Section2
\nElse
\nGoTo Section1
\nEnd If<\/p>\n

Section2:
\ni = i + 1<\/p>\n

Next
\nApplication.StatusBar = False<\/p>\n

End Sub<\/p><\/blockquote>\n

\"\"<\/p>\n

duplicates-random-lists.zip<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

I often like to fill my excel model and workbooks with data to test out\u00a0functionality and what better than a random\u00a0range of numbers. (hint look in my other post about changing negative numbers\u00a0to positives and vice versa if you want to make the them different sizes for instance if you wanted the numbers in the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[26],"tags":[123,79,78,117,27,119,121],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/posts\/127"}],"collection":[{"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/comments?post=127"}],"version-history":[{"count":0,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/posts\/127\/revisions"}],"wp:attachment":[{"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/media?parent=127"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/categories?post=127"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/tags?post=127"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}