Using VBA to generate random numbers

**matt3542** · 09-01-2008, 11:48 AM

Dear Forum,

I am using the VBA code below (bottom) to return random numbers for a selected area of cells where the range of random numbers is between 1 and the
total number of cells. Unfortunately sometimes the random numbers appear more than once and I would like to amend this to ensure duplicates are not included, any help would be greatly appreciated, thanks, Matt

Ps I have already tried substituting with VBUniqRandInt() but it returns #NAME? in every selected cell??, i.e;

Please Login or Register  to view this content.

**shg** · 09-01-2008, 11:52 AM

I have edited your post to add Code Tags.

Please read the Forum Rules.

**shg** · 09-01-2008, 11:56 AM

You can enter the function below as an array formula in the range where you wish non-repeated numbers to appear. For example, in A1:A10, enter =RandSeq()

Array formulas MUST be confirmed with Ctrl+Shift+Enter, not Enter. You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.

Please Login or Register  to view this content.

**matt3542** · 09-01-2008, 12:49 PM

Hi Shg,

Advance apologies but I am a complete novice with VBA and as such I'm not sure I entirely understand your approach. The vba code I used originally (below) relied on a user selecting a range of cells and then when the code was run it generated and populated the selected cells with random (albeit not non-repeating) numbers. Should I add your code as a seperate function and then run as a sub proc replacing the "=int(Rand() ..with "=int(RandSeq()?

Ta, Matt

original code thats giving duplicated values -

Sub MakeRandomTable4()

For Each r In Selection
r.Formula = "=int(Rand() *" & Selection.Count & "+1)"
Next
End Sub

**shg** · 09-01-2008, 01:14 PM

Matt!

Please read the Forum Rules and then edit your post to wrap your code with Code Tags.

You select the range of cells in which you want the numbers to appear, and then array-enter the formula. Read the first line in my last post. But first, WRAP YOUR CODE WITH CODE TAGS.

**matt3542** · 09-02-2008, 08:39 AM

Hi Shg,

Sorry for not using the proper conventions when editing code, I'm very new to these forums but I will ensure I use code tags within any future posts. Also thankyou for giving me a solution that worked as intended - I persevered and managed to understand how to apply it as you described, much appreciated.
Best rgds
Matt

**Cheeky Charlie** · 09-02-2008, 09:15 AM

Originally Posted by shg

Matt!

Please read the Forum Rules and then edit your post to wrap your code with Code Tags.

You select the range of cells in which you want the numbers to appear, and then array-enter the formula. Read the first line in my last post. But first, WRAP YOUR CODE WITH CODE TAGS.

I'm impressed, that exclamation mark is far more discreet than what I might have written...