how to choose random namber form loop?

[pm]

hello,

i have simple loop

For i = 1 to 100
call something
Next i

and i'd like to run this loop on 20 random "i" numbers without repetition
eg: 74, 3, 32, 1, 2, 15, 92, 22, and so on...

how can it be worked out?

rgs

 

[Harald Staff]

Hi

Either
generate a collection of N non-duplicate random numbers and loop that
collection
or
remember each used random number and check if it has been used previously
before using it.

There are probably other approaches as well.

HTH. Best wishes Harald

 

[pm]

Either
generate a collection of N non-duplicate random numbers and loop that
collection

i guess - generate in cells or in some array in VBA?

or
remember each used random number and check if it has been used previously
before using it.

how VBA may remember previously used i-values? i generate those number without
writing them into cells in sheet

i'd like to use random numebrs only in VBA module..

 

[Tushar Mehta]

See
Random Selection
http://www.tushar-mehta.com/excel/newsgroups/rand_selection/

particularly,
Selecting a random subset without repeating -- using a user defined function
(UDF)
http://www.tushar-mehta.com/excel/newsgroups/rand_selection/vba.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Harald Staff]

i guess - generate in cells or in some array in VBA?

Here's a basic solution, using integers 1 to 20. Imothere's rarely any
reason not to use a hidden sheet's cells for these things, sheets sort and
calculate far faster than code does. But this is fast enough for a small
amount of items:

'********** top of module ***********

Type Token
L As Long
SortNum As Double
End Type

Sub Lottery()
Dim Tokens(1 To 100) As Token
Dim Tmp As Token
Dim i As Long
Dim M As Long, N As Long
'assign values:
Randomize
For i = 1 To 100
Tokens(i).L = i
Tokens(i).SortNum = Rnd()
Next
'sort by sortnum:
For M = 1 To 99
For N = 1 To 99
If Tokens(N).SortNum > Tokens(N + 1).SortNum Then
Tmp.L = Tokens(N).L
Tmp.SortNum = Tokens(N).SortNum
Tokens(N).L = Tokens(N + 1).L
Tokens(N).SortNum = Tokens(N + 1).SortNum
Tokens(N + 1).L = Tmp.L
Tokens(N + 1).SortNum = Tmp.SortNum
End If
Next
Next
'pick top 20:
For i = 1 To 20
MsgBox Tokens(i).L, , "Token " & i
Next
End Sub

HTH. Best wishes Harald