random numbers repeating

[John T]

Hi folks

I'm using random number generation using randbetween(0,10).

Is there a way of stopping it from displaying the same number twice in
a row?

If replying by email take out "the rubbish" !

John T
webmaster of John Taylor's Freebies URL www . johnandgwyn. co.uk

 

[L. Howard Kittle]

Try this, from a Google search. Code is by Bob Phillips.

Enter 10 in F1 of sheet 1, and the random numbers will be in A1:A10.

Sub GetThem()
Dim j As Long
Dim N As Long
Const LNG_PLUG As Long = 999
Dim NUM_GENERATED As Long
Dim arrCheck() As Long
Dim arrList() As Long


NUM_GENERATED = Sheets(1).Range("F1").Value


ReDim arrCheck(1 To NUM_GENERATED + 1)
ReDim arrList(1 To NUM_GENERATED)


j = 1
Do While j < NUM_GENERATED
'Get a random number
Randomize
N = Int(Rnd * NUM_GENERATED + 1)
'If number unique then add to arrList.
If arrCheck(N) <> LNG_PLUG Then
arrList(j) = N
arrCheck(N) = LNG_PLUG
j = j + 1
End If
Loop
Sheets(1).Range("A1:A" & NUM_GENERATED).Value = _
Application.Transpose(arrList())
End Sub

HTH
Regards,
Howard

 

[John T]

Yes it works but it doesn't achieve what I'm after.

What I need to do is to generate a single random number each time [F9]
is pressed, without getting the same number on two consecutive
presses.


Thanks for trying

Regards

John T

 

[Guest]

give us more information. what do you realy want to do

 

[L. Howard Kittle]

Hi John,

Give this a try, assigned to a forms button.

Sub OneRandNum()
Dim j As Integer
line1:
j = Int((11 * Rnd) + 0)
'Range("E50").End(xlUp).Offset(1, 0).Value = j
If Range("F1").Value = j Then
GoTo line1
ElseIf Range("F1").Value <> j Then
Range("F1") = j
End If
End Sub

My tests seem to comply with what you want. You can un-edit out the line
with the End(xlUp) and ALL numbers generated will list in column E, while
the target non-duplicated number is in F1. You then can see when there are
successive numbers in column E, the code produces another number until there
is no match to F1 upon which it lists it in F1.

HTH
Regards,
Howard

 

[Harlan Grove]

John T wrote...

I'm using random number generation using randbetween(0,10).

Is there a way of stopping it from displaying the same number twice in
a row?

No, because there's nonzero probability that drawing from 0..10 with
replacement will produce duplicates.

If you could live with circular recalc, run the menu command Tools >
Options, select the Calculation tab in the Options dialog, check the
Iteration box and set Maximum iterations to 1. Then try the following
formula in cell A1.

=MOD(A1+RANDBETWEEN(1,10),11)

 

[John T]

On Tue, 23 Jan 2007 12:27:18 -0800, "L. Howard Kittle"




Still not working for me
I've emailed you the actual file in case you fance seeing exactly what
I'm trying to achieve.

Apologies for being a cheeky b*gg*r

 

[John T]

John T wrote...

No, because there's nonzero probability that drawing from 0..10 with
replacement will produce duplicates.

If you could live with circular recalc, run the menu command Tools >
Options, select the Calculation tab in the Options dialog, check the
Iteration box and set Maximum iterations to 1. Then try the following
formula in cell A1.

=MOD(A1+RANDBETWEEN(1,10),11)

Thanks for your suggestion, it almost works but throws up other
problems in the file.

Cheers

John T

To reply by email take out "the rubbish"

 

[Guest]

The standard way do sampling without replacement is to list the available
values (0,1,...,10), in the next column put random numbers (e.g. =RAND(), but
then Copy/Paste Special Values to keep them from recalculating). Sort both
columns by the random numbers, then select from the now randomized list of
integers between zero and ten.

Jerry

 

[John T]

PROBLEM SOLVED thanks to email interaction with L Howard Kittle !


Aren't Internet communities fantastic!

John T

 

[SteveW]

You might enlight us with the solution then

'cos I didn't get his email !

Steve

 

[L. Howard Kittle]

Hi Steve,

I used this macro to generate the non-repeating random numbers from 0 to 10.

Sub OneRandNum()
Dim j As Integer
line1:
j = Int((11 * Rnd) + 0)
'Range("E50").End(xlUp).Offset(1, 0).Value = j
If Range("F1").Value = j Then
GoTo line1
ElseIf Range("F1").Value <> j Then
Range("F1") = j
End If
End Sub

I also submitted a lookup formula that converted the numeral to a text
version, the news group may not have been privy to this need as he sent me a
workbook. So if an 8 came up, a cell on the sheet returned "eight", a 6,
then "six".

Formula was a simple loolup...(all on one line of course)

=LOOKUP(F1,{0,1,2,3,4,5,6,7,8,9,10},{"zero","one","two","three","four","five","six","seven","eight","nine","ten"})

Turns out he wanted 1 to ten, so changed "j = Int((11 * Rnd) + 0)" to
"j = Int((10 * Rnd) + 1)" and deleted the 0 and zero lookup from the
formula.

=LOOKUP(F1,{1,2,3,4,5,6,7,8,9,10},{"one","two","three","four","five","six","seven","eight","nine","ten"})

OP is now a happy camper. Once I saw the workbook, his stuff made sense.

Regards,
Howard

 

[SteveW]

Thanks for that

Hi Steve,

I used this macro to generate the non-repeating random numbers from 0 to
10.

Sub OneRandNum()
Dim j As Integer
line1:
j = Int((11 * Rnd) + 0)
'Range("E50").End(xlUp).Offset(1, 0).Value = j
If Range("F1").Value = j Then
GoTo line1
ElseIf Range("F1").Value <> j Then
Range("F1") = j
End If
End Sub

I also submitted a lookup formula that converted the numeral to a text
version, the news group may not have been privy to this need as he sent
me a
workbook. So if an 8 came up, a cell on the sheet returned "eight", a
6,
then "six".

Formula was a simple loolup...(all on one line of course)

=LOOKUP(F1,{0,1,2,3,4,5,6,7,8,9,10},{"zero","one","two","three","four","five","six","seven","eight","nine","ten"})

Turns out he wanted 1 to ten, so changed "j = Int((11 * Rnd) + 0)" to
"j = Int((10 * Rnd) + 1)" and deleted the 0 and zero lookup from the
formula.

=LOOKUP(F1,{1,2,3,4,5,6,7,8,9,10},{"one","two","three","four","five","six","seven","eight","nine","ten"})

OP is now a happy camper. Once I saw the workbook, his stuff made sense.

Regards,
Howard

 

[John T]

This is Howard's macro:

Sub OneRandNum()
Dim j As Integer
line1:
j = Int((10 * Rnd) + 1)
'Range("E50").End(xlUp).Offset(1, 0).Value = j
If Range("b8").Value = j Then
GoTo line1
ElseIf Range("b8").Value <> j Then
Range("b8") = j
End If
End Sub

"B8" is the cell chosen to have the random number each time the sheet
recalculates. The completedm working file can be downloaded from
www.johnandgwyn.co.uk - when you ENTER the site ther's a direct link
in the "Latest news" section to "On Screen Precision Teaching Probe"


cheers

John T

to reply by email take out "the rubbish"