Random formula without clashing with each cell.

[Kingkoopa]

Hi Guys,

I am currently using windows 7 and excel 2010.

I have the following formula in Cell A & Cell B : RANDBETWEEN(0,5)

How do I make it so when I press the refresh button the random number chosen would not clash with each other?

Example :

I want to avoid having 0,0 1,1 2,2 3,3 4,4 5,5.

So if cell A generates the number 1. Cell B will not generate number 1. I think I have to combine with somekind of If formula?

Thank you

 

[AlanY]

one way to do it

Excel 2013/2016
	A	B	C	D	E	F
1	0.039682	0.550138	0.322384	0.920948	0.378305	0.07154
2
3	5	1
Sheet1

Cell Formulas
Range	Formula
A1	=RAND()
A3	=RANK(A1,$A$1:$F$1)-1

 

[Kingkoopa]

Hi Alan,

It worked thanks! But any idea how to input them in VBA code?

My current code :

[Sub NumberGenerator()
Range("A3") = WorksheetFunction.RandBetween(0, 5)
Range("B3") = WorksheetFunction.RandBetween(0, 5)

Call test
End Sub

Sub test()
Scheduledtime = Now + TimeValue("00:00:10")
Application.OnTime Scheduledtime, "NumberGenerator"
End Sub

I tried modifying into :
Range("A3") = WorksheetFunction.RANK(A3,$A$1:$F$1)-1
Range("B3") = WorksheetFunction.RANK(B3,$A$1:$F$1)-1

But got an invalid error.

 

[NARAYANK991]

Hi ,

Try this simple code :

Public Sub PickTwoRandomNumbers()
           With Me
                .[A3] = Int(Rnd() * 6)
                
                Randomize
                Do
                   .[B3] = Int(Rnd() * 6)
                Loop Until .[A3] <> .[B3]
           End With
End Sub

The code has to be placed in the worksheet section where you want the numbers placed ; if you place this in a code module , replace the keyword Me with the keyword ActiveSheet.

 

[Rick Rothstein]

This routine guarantees the two numbers are random between 0 and 5 and never equal to each other...

[table="width: 500"]
[tr]
	[td]Sub TwoNonEqualRandomNumbersBetween0and5()
  Dim Cnt As Long, RndIndx As Long, Tmp As Long, Arr As Variant
  Randomize
  Arr = [{0,1,2,3,4,5}]
  For Cnt = 6 To 1 Step -1
    RndIndx = Int(Cnt * Rnd) + 1
    Tmp = Arr(RndIndx)
    Arr(RndIndx) = Arr(Cnt)
    Arr(Cnt) = Tmp
  Next
  Range("A3:B3") = Evaluate("{" & Arr(1) & "," & Arr(2) & "}")
End Sub[/td]
[/tr]
[/table]