Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to choose random namber form loop?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to choose random namber form loop?
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" skrev i melding ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to choose random namber form loop?
Harald Staff wrote:
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.. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to choose random namber form loop?
See
Random Selection http://www.tushar-mehta.com/excel/ne...and_selection/ particularly, Selecting a random subset without repeating -- using a user defined function (UDF) http://www.tushar-mehta.com/excel/ne...ction/vba.html -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to choose random namber form loop?
"pm" skrev i melding ...
Harald Staff wrote: 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? 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I choose more than one item from list in a form in Excel? | Excel Worksheet Functions | |||
Excel range choose button in VBA form | Excel Programming | |||
loop through form objects | Excel Programming | |||
loop random number | Excel Programming | |||
random number generation: loop | Excel Programming |