How would I go about calculating random numbers using a low and high number
boundary? I found the RANDBETWEEN function and this is apparently exactly
what I am looking for, but it does not seem to work in VBA. I am very new to
VBA and any suggestions would be welcome.

From Excel online help

MyValue = Int((6 * Rnd) + 1) ' Generate random value between 1 and 6.


--
Ian
--
"GSpline" wrote in message
...
How would I go about calculating random numbers using a low and high
number
boundary? I found the RANDBETWEEN function and this is apparently exactly
what I am looking for, but it does not seem to work in VBA. I am very new
to
VBA and any suggestions would be welcome.

Thanks for the info, Ian. I am not having much luck with this particular
question in the online help. What you posted is close to what I am looking
for, but I am needing the random value to be between two numbers other than
1, for example a random number between 9 and 11. I will keep plugging away,
it looks like this gives me a starting point now.

GSpline
"Ian" wrote:

From Excel online help

MyValue = Int((6 * Rnd) + 1) ' Generate random value between 1 and 6.


--
Ian
--
"GSpline" wrote in message
...
How would I go about calculating random numbers using a low and high
number
boundary? I found the RANDBETWEEN function and this is apparently exactly
what I am looking for, but it does not seem to work in VBA. I am very new
to
VBA and any suggestions would be welcome.

Ok, using what you talked about I was able to come up with the following:

MyValue = Int((2 * Rnd) + 1) + 6

This gives me a random range of 7-9. This probably is not a "clean" way to
do this calculation, is there a better way to generate a random number
between 7 & 9 as in my example?

Thanks again.


"Ian" wrote:

From Excel online help

MyValue = Int((6 * Rnd) + 1) ' Generate random value between 1 and 6.


--
Ian
--
"GSpline" wrote in message
...
How would I go about calculating random numbers using a low and high
number
boundary? I found the RANDBETWEEN function and this is apparently exactly
what I am looking for, but it does not seem to work in VBA. I am very new
to
VBA and any suggestions would be welcome.

Modify Ian's code slightly:

MyValue = Int(3 * RND()) + 9


In article ,
"GSpline" wrote:

Thanks for the info, Ian. I am not having much luck with this particular
question in the online help. What you posted is close to what I am looking
for, but I am needing the random value to be between two numbers other than
1, for example a random number between 9 and 11. I will keep plugging away,
it looks like this gives me a starting point now.

Oops, typo. This should read as:

MyValue = Int((3 * Rnd) + 1) + 6

so that it gives a range of 7-9

"GSpline" wrote:

Ok, using what you talked about I was able to come up with the following:

MyValue = Int((2 * Rnd) + 1) + 6

This gives me a random range of 7-9. This probably is not a "clean" way to
do this calculation, is there a better way to generate a random number
between 7 & 9 as in my example?

Thanks again.


"Ian" wrote:

From Excel online help

MyValue = Int((6 * Rnd) + 1) ' Generate random value between 1 and 6.


--
Ian
--
"GSpline" wrote in message
...
How would I go about calculating random numbers using a low and high
number
boundary? I found the RANDBETWEEN function and this is apparently exactly
what I am looking for, but it does not seem to work in VBA. I am very new
to
VBA and any suggestions would be welcome.

Could we say that if we wanted to write out the rule for performing this
particular type of calculation, that if we wanted to generate a random number
(MyValue) between two other numbers (x, y) and yx :
MyValue = Int( ( ( (y-x)+1)*RND() ) + (x-1) )

This should work for most positive random numbers that we wish to find
random numbers between, right?


"JE McGimpsey" wrote:

Modify Ian's code slightly:

MyValue = Int(3 * RND()) + 9


In article ,
"GSpline" wrote:

Thanks for the info, Ian. I am not having much luck with this particular
question in the online help. What you posted is close to what I am looking
for, but I am needing the random value to be between two numbers other than
1, for example a random number between 9 and 11. I will keep plugging away,
it looks like this gives me a starting point now.

From VBA Help ("Rnd Function"):

To produce random integers in a given range, use this formula:

Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

Here, upperbound is the highest number in the range, and lowerbound is the
lowest number in the range.


In article ,
"GSpline" wrote:

Could we say that if we wanted to write out the rule for performing this
particular type of calculation, that if we wanted to generate a random number
(MyValue) between two other numbers (x, y) and yx :
MyValue = Int( ( ( (y-x)+1)*RND() ) + (x-1) )

This should work for most positive random numbers that we wish to find
random numbers between, right?

Thanks

"JE McGimpsey" wrote:

From VBA Help ("Rnd Function"):

To produce random integers in a given range, use this formula:

Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

Here, upperbound is the highest number in the range, and lowerbound is the
lowest number in the range.


In article ,
"GSpline" wrote:

Could we say that if we wanted to write out the rule for performing this
particular type of calculation, that if we wanted to generate a random number
(MyValue) between two other numbers (x, y) and yx :
MyValue = Int( ( ( (y-x)+1)*RND() ) + (x-1) )

This should work for most positive random numbers that we wish to find
random numbers between, right?

I found the RANDBETWEEN function ...
..., but it does not seem to work in VBA. I am very new to
VBA and any suggestions would be welcome.

Hi. Just to add.
On the vba issue, go to the vba editor.
On the menu, do Tools | References...
and select "atpvbaen.xls"
(name means: Analysis Tool Pak, VBA, US)

Now, the following code should work:

Dim n
n = RANDBETWEEN(1, 10)

HTH :)
--
Dana DeLouis
Win XP & Office 2003


"GSpline" wrote in message
...
How would I go about calculating random numbers using a low and high
number
boundary? I found the RANDBETWEEN function and this is apparently exactly
what I am looking for, but it does not seem to work in VBA. I am very new
to
VBA and any suggestions would be welcome.

In article ,
says...
How would I go about calculating random numbers using a low and high number
boundary? I found the RANDBETWEEN function and this is apparently exactly
what I am looking for, but it does not seem to work in VBA. I am very new to
VBA and any suggestions would be welcome.

The general rule to generate random integers between a and b, both
inclusive and a < b, is a + Int(Rnd()*(b-a+1))

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

try something like this
Sub Macro1()
Range("A1").Select
Selection.FormulaR1C1 = "=RANDBETWEEN(1,10)"
Selection.AutoFill Destination:=Range("A1:A16"), Type:=xlFillDefault
Range("A1:A16").Select
End Sub

Tushar Mehta wrote in message
om...
In article ,
says...
How would I go about calculating random numbers using a low and high
number
boundary? I found the RANDBETWEEN function and this is apparently
exactly
what I am looking for, but it does not seem to work in VBA. I am very
new to
VBA and any suggestions would be welcome.

The general rule to generate random integers between a and b, both
inclusive and a < b, is a + Int(Rnd()*(b-a+1))

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions