• Status: Solved
• Priority: Medium
• Security: Public
• Views: 331

# Random Number Generator with Conditions

EE Professionals,

I'm creating a demonstration macro of a model where I need to generate two columns of numbers randomly but with certain conditions.  Each column contains 15 cells (15 rows), that need to be populated when the subroutine is run.  The numbers in the first column must fall between 1 and 10 and always be less then the numbers in the second column. So in Column 1 you may have a randomly generated 6 which means in Column 2 the number would have to be 6, 7, 8, 9 or 10.

B.
0
Bright01
• 3
• 3
• 2
1 Solution

Could the second column not just have

=(your current formula) +A1  ( Assumes this is in B1, adjust accordingly)

Added to the end of the formula? Therefore just adding the value in the first column onto the random generated value, thus is ALWAYS greater than the value in the first column?
0

OR

calculate value for A1, enter it. Then have B1...

=RANDBETWEEN(A1,uppervalue)
0

Commented:
See the following ... I have assumed the upper limit for column B, (intHigher) as 20 ... it MUST be different to the 1 ... 10 limitation on column A as column B is always higher but tweak if necessary within the premise that inthigher is always greater than inthigh

Outputs are made to the active sheet as it stands.

Chris
``````Sub twoRand()
Dim num As Integer
Dim intLow As Integer
Dim intHigh As Integer
Dim intHigher As Integer
Dim arr(1 To 15, 1 To 2) As Integer
Dim itm As Integer

Randomize
intLow = 1
intHigh = 10
intHigher = 20
For itm = 1 To 15
arr(itm, 1) = Int((intHigh - intLow + 1) * Rnd + intLow)
arr(itm, 2) = Int((intHigher - arr(itm, 1) + 2) * Rnd + arr(itm, 1) + 1)
Next
ActiveSheet.Range("a1").Resize(15, 2) = arr

End Sub
``````
0

Author Commented:
Neilsr,

Thanks for the note, but I don't follow your logic.  I have no formula; I'm looking for a macro and a formula to drive the random sequencing.

Chris,

I used your formula and it works; however, when I generate the random list, it gives me values in column B that are greater then 10.  Here is the code I modified.  Can you see how I can limit and force a random sample higher then the values in Col. A but = or less then 10?

Thanks,

B.

Sub twoRand()
Dim num As Integer
Dim intLow As Integer
Dim intHigh As Integer
Dim intHigher As Integer
Dim arr(1 To 15, 1 To 2) As Integer
Dim itm As Integer

Randomize
intLow = 1
intHigh = 10
intHigher = 10
For itm = 1 To 10
arr(itm, 1) = Int((intHigh - intLow + 1) * Rnd + intLow)
arr(itm, 2) = Int((intHigher - arr(itm, 1) + 2) * Rnd + arr(itm, 1) + 1)
Next
ActiveSheet.Range("a1").Resize(15, 2) = arr

End Sub
0

Author Commented:
Chris,

I got it to work by playing around with  it.  Much thanks for the assistance on it.  Works great!

B.
0

Commented:
For info you fell foul of your description.  Your latter comment indicates that column B is also limited to 10 whereas the description stated column B is always greater than column A.

Since column A as stated ranges from 1 to 10 then it followed that column B would be greater than 10!  Assuming that column B is greater than or equal to column A then the line changes from:

arr(itm, 2) = Int((intHigher - arr(itm, 1) + 2) * Rnd + arr(itm, 1) + 1)
to

arr(itm, 2) = Int((intHigher - arr(itm, 1) + 1) * Rnd + arr(itm, 1))

With inthigher refined as 10, making the easiest edit.

Chris

Chris
0

Author Commented:
Chris,

Not sure I understand your comment.  In my original description I said,  "The numbers in the first column must fall between 1 and 10 and always be less then the numbers in the second column."  This would mean that the highest number in column 1 could only be 9 leaving room for 10 in column 2 and satisfying the description.  Perhaps I didn't word it well.  My bad.

Anyway, thanks to your efforts, it works and works well.

Again, much thanks.

B.
0

Commented:
Happy to accept I misunderstood I took 1 to 10 to be the range 1 ... 10 whereas it sounds as though maybe it should have been 2 ... 9, often clarity of function is the hardest thing to impart.

I did see the potential issue at the outset so when I made my first post I qualified my assumption about column 1 being 1 ... 10 ... nonetheless whichever route you went to modify the code to your needs - well done for successfully making the change.

Chris
0

## Featured Post

• 3
• 3
• 2
Tackle projects and never again get stuck behind a technical roadblock.