# generate a random number to enter in a cell

#### brianfosterblack

##### Board Regular
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

Last edited:

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### Paul Ked

##### Active Member
Code:
``````Function RandNum()
Randomize
RandNum = Int((Range("I11") - Range("I9") + 1) * Rnd + Range("I9"))
End Function``````

#### brianfosterblack

##### Board Regular
Thank you this works perfectly

Much appreciated

#### Paul Ked

##### Active Member #### joeu2004

##### Well-known Member
Simply:

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

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

Code:
``````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.

Last edited:

#### Paul Ked

##### Active Member
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!

Last edited:

#### shg

##### MrExcel MVP
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

##### Active Member
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()

Last edited:

#### shg

##### MrExcel MVP
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.

Last edited:

#### Paul Ked

##### Active Member
Thanks for clarification 1,089,559
Messages
5,408,971
Members
403,246
Latest member
NateD1