unique random number in VBA help

[Dabaron2321]

Hi i'm using the following macro that is set to run every time i press my form button:

Sub One()


Range("b1:B1000").Value = Int((200 - 1 + 1) * Rnd + 1)

End Sub

the problem is when i press the button each cell brings back the same result, ie. all the cells display 154 then when pressed again all show 86 then all show 5 etc, when i want all the cells between b1 and b1000 to function independent to each other, yet i don't want to have run a separate formula for each cell? Any advice?

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG14Jul27
[COLOR="Navy"]Dim[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Range("b1:B1000")
    R.Value = Int((200 - 1 + 1) * Rnd + 1)
[COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Trevor G]

Try this:

Sub fillRandomNumbers4A()Dim cell As Range
For Each cell In Range("C3:H14")
Dim Low As Double
Dim High As Double
Low = 11 '<<< CHANGE AS DESIRED
High = 20 '<<< CHANGE AS DESIRED
cell = Int((High - Low + 1) * Rnd() + Low)
Next cell
End Sub

 

[Dabaron2321]

Yes thats perfect, many thanks!

 

[MickG]

You're welcome