Solved: rand numbers

[chungtinhlak]

Hi, i been thinking of this but couldn't think of an easy way to do it. Please help me.

if I want my cells

A1 to A6 to have a random number from 1 - 6, meaning, if A1 value is 6, then A 2 - 5 cannot be 6.

thanks

[GTO]

Greetings,

Try:

Option Explicit
Sub Populate_Rnds()
Dim col As New Collection
Dim i As Integer
Dim intRandBetween As Integer

Randomize

Do
On Error Resume Next

intRandBetween = Int((6 - 1 + 1) * Rnd + 1)

col.Add intRandBetween, CStr(intRandBetween)
On Error GoTo 0
Loop While col.Count < 6

For i = 1 To 6
Range("A" & i).Value = col(i)
Next
End Sub

Hope this helps,

Mark

[Bob Phillips]

First, ensure cell B1 is empty and goto Tools>Options and on the Calculation
tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell A1
=IF(($B$1="")+(AND(A1>0,COUNTIF($A$1:$A$6,A1)=1)),A1,INT(RAND()*6+1))
it should show a 0

Copy A1 down to A6.

Finally, put some value in B1, say an 'x', and all the random numbers will
be generated, and they won't change.

To force a re-calculation, clear cell B1, edit cell A1, don't change it,
just edit to reset to 0, copy A1 down to A6, and re-input B1.

[chungtinhlak]

wow thanks xld and gto, they both work really well. I am still trying to diggest the two and see how it works. Thank you so much for all your help.