• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Randbetween VBA

V

Vishal Srivastava

Member
Hello Friends,
While doing an assignment, I got stucked on Randbetween function using VBA. My problem is that I want to generate random no. from 10 to 50 with the random interval of 5.
Result should be as:
15, 10, 25 ,40, 45, 35, etc.


How could I achieve the same in VBA. Please help.

Thanks & Regards,
CMA Vishal Srivastava
 
Vishal Srivastava said:
Hello Friends,
While doing an assignment, I got stucked on Randbetween function using VBA. My problem is that I want to generate random no. from 10 to 50 with the random interval of 5.
Result should be as:
15, 10, 25 ,40, 45, 35, etc.


How could I achieve the same in VBA. Please help.

Thanks & Regards,
CMA Vishal Srivastava
Click to expand...
Hi,

Try this code. If you want the numbers to be unique then remove the comment marks from the 2 lines of the do loop.


Code: 
Sub somesub()
Dim FillRange As Range, c As Range
Set FillRange = Range("A1:A6")
For Each c In FillRange
'Do
c.Value = Round(Int((50 * Rnd) + 10) / 5) * 5
'Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
End Sub
 
Last edited by a moderator:
Hi Vishal ,

Try this :
Code: 
Sub somesub1()
    Dim FillRange As Range, c As Range
    Set FillRange = Range("A1:A6")
    For Each c In FillRange
        Do
          Do
              temp = Application.WorksheetFunction.RandBetween(10, 50)
          Loop Until temp Mod 5 = 0
          c.Value = temp
        Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
    Next
End Sub
Narayan
 
Mike H.. said:
Hi,

Try this code. If you want the numbers to be unique then remove the comment marks from the 2 lines of the do loop.


Code: 
Sub somesub()
Dim FillRange As Range, c As Range
Set FillRange = Range("A1:A6")
For Each c In FillRange
'Do
c.Value = Round(Int((50 * Rnd) + 10) / 5) * 5
'Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
End Sub
Click to expand...


NARAYANK991 said:
Hi Vishal ,

Try this :
Code: 
Sub somesub1()
    Dim FillRange As Range, c As Range
    Set FillRange = Range("A1:A6")
    For Each c In FillRange
        Do
          Do
              temp = Application.WorksheetFunction.RandBetween(10, 50)
          Loop Until temp Mod 5 = 0
          c.Value = temp
        Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
    Next
End Sub
Narayan
Click to expand...


Thanks MIKE and Narayan,
Both solutions worked perfectly.

Thanks again.


CMA Vishal Srivastava
 
Hi Vishal ,

I think you need to recheck ; the RANDBETWEEN function will always return a random number between the specified limits , which in the formula given by Hui are 2 and 10. Multiplying the resulting number by 5 can only result in a number between 10 and 50 , which are your lower and upper limits.

In fact the RND function returns a number between 0 and .99999 , which when used in the following formula :

Round(Int((50 * Rnd) + 10) / 5) * 5

will result in numbers above your upper limit ; as an example , if the RND function returns 0.9 , 50 *0.9 gives 45 , which when added to 10 results in 55 ; this divided by 5 and then multiplied by 5 gives 55 !

Narayan
 
NARAYANK991 said:
Hi Vishal ,

I think you need to recheck ; the RANDBETWEEN function will always return a random number between the specified limits , which in the formula given by Hui are 2 and 10. Multiplying the resulting number by 5 can only result in a number between 10 and 50 , which are your lower and upper limits.

In fact the RND function returns a number between 0 and .99999 , which when used in the following formula :

Round(Int((50 * Rnd) + 10) / 5) * 5

will result in numbers above your upper limit ; as an example , if the RND function returns 0.9 , 50 *0.9 gives 45 , which when added to 10 results in 55 ; this divided by 5 and then multiplied by 5 gives 55 !

Narayan
Click to expand...
Hi Narayan,
Thanks for explanation. I havn't think of Rnd funtion in that way.


CMA Vishal Srivastava
 
You must log in or register to reply here.
Back
Top