generate a random number to enter in a cell

[brianfosterblack]

I am am looping through a range on my computer (Excel 2013) and where I find a number 1 I want to replace it with a random number between the bottom number in Cell I9 and the top number in cell I11
So what I need my code to do is Activecell.value= "Randbetween Range ("I9").value and Range ("I11").Value
Can someone help with the code to do this?
The number generated must be a whole number

 

[Paul Ked]

Function RandNum()
    Randomize
    RandNum = Int((Range("I11") - Range("I9") + 1) * Rnd + Range("I9"))
End Function

 

[brianfosterblack]

Thank you this works perfectly

Much appreciated

 

[Paul Ked]

 

[joeu2004]

Simply:

ActiveCell = WorksheetFunction.RandBetween(Range("I9"), Range("I11"))

Although, if you are "looping through a range", there is no need to continuously change ActiveCell.

Dim r As Range
For each r in myRange
    If r = 1 Then r = WorksheetFunction.RandBetween(Range("I9"), Range("I11"))
Next

It is better to use RandBetween than VBA Rnd. The Excel RNG is better than VBA Rnd, since Excel 2010 (2007?). I assume the faster execution of VBA Rnd is not a factor.

PS.... For efficiency, it is better to minimize access to the Excel cells. So the following is better:

Dim lo As Long, hi As Long
lo = Range("I9")
hi = Range("I11")
[....] WorksheetFunction.RandBetween(lo, hi)

Change Long to Double if lo and hi might exceed the integer limit of type Long (!).

Also, that does assume that I9 and I11 contain integers (or you want them rounded to integers). After all, you indicated that you wanted to use RANDBETWEEN, in the first place.

 

[Paul Ked]

It is better to use RandBetween than VBA Rnd. The Excel RNG is better than VBA Rnd, since Excel 2010 (2007?).

Please explain the difference. As far as I know, random means random!

 

[shg]

Lots.

The VBA Rnd() function is about 1000 times faster in VBA than WorksheetFunction.Rand.

Rnd() has a very short cycle (2^24). In Excel 2007, RAND() used a linear congruential generator with a period of ~ 2^44. In Excel 2010, RAND was changed to use a Mersenne Twister algorithm with a ridiculously long period (~2^19937)

Rnd() can be initialized to restart with any number in the pattern, which is convenient for test. RAND() cannot.

 

[Paul Ked]

I knew VBA Rnd() was superior to Rand, and so thought RandBetween would be in the same boat as Rand as it is a worksheet function. That is why I asked the difference between RandBetween and Rnd()

 

[shg]

It's superior only in speed and the ability to initialize it.

It's useless for generating unpredictable random sequences (e.g., dice rolls), because after observing a few rolls (maybe 20), it's possible to predict all the rest unless it is re-randomized.

EDIT: I don't know how RANDBETWEEN() works; I assume it uses the MT RAND() function, but don't know that for a fact.

 

[Paul Ked]

Thanks for clarification