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.

=(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?

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 IntegerDim intLow As IntegerDim intHigh As IntegerDim intHigher As IntegerDim arr(1 To 15, 1 To 2) As IntegerDim 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) = arrEnd Sub

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

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:

With inthigher refined as 10, making the easiest edit.

Chris

Chris

0

Bright01Author 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.

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

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

=(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?