[SOLVED] Trouble with RANDBETWEEN

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 <[email protected]>,
"GSpline" <[email protected]> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 y>x :
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 <[email protected]>,
> "GSpline" <[email protected]> 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 <[email protected]>,
"GSpline" <[email protected]> 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 y>x :
> 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 <[email protected]>,
> "GSpline" <[email protected]> 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 y>x :
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
[email protected] 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 <[email protected]> wrote in message
news:[email protected]...
> In article <[email protected]>,
> [email protected] 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