generate a random number to enter in a cell

brianfosterblack

Board Regular
Joined
Nov 1, 2011
Messages
133
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:

Some videos you may like

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
Joined
Jun 4, 2015
Messages
442
Code:
Function RandNum()
    Randomize
    RandNum = Int((Range("I11") - Range("I9") + 1) * Rnd + Range("I9"))
End Function
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,630
Office Version
2010
Platform
Windows
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
Joined
Jun 4, 2015
Messages
442
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
Joined
May 7, 2008
Messages
21,736
Office Version
2010
Platform
Windows
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
Joined
Jun 4, 2015
Messages
442
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
Joined
May 7, 2008
Messages
21,736
Office Version
2010
Platform
Windows
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:

Forum statistics

Threads
1,089,559
Messages
5,408,971
Members
403,246
Latest member
NateD1

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top