R
rjmachado
# NEW CHALLENGE: HOW TO PRODUCE combinations of different random
numbers
AND DO NOT FULLY single repeatable in Excel 2003?
Well I found a function that produces random numbers different
each other. The only problem is that since they need to apply
function repeatedly through a cycle of VBA macro (DO. ... LOOP) it
(a function that returns random numbers different from each other)
combinations can produce duplicate (eg 1,2,3,4,5) (, (7, 10, 11, 4),
[1.2.3.4.5) ...<-- COMBINATION DUPLICATE!) And I would like to know
how to do that is avoid this and produze
randomly all the different possible combinations, knowing that
each combination is comprised of 5 different random numbers from 1
at 50 followed by 2 different random numbers 1 through 9 but
independent of the previous (eg 46,47,48,49,50,8,9 (); other
example: 1,2,3,4,5,1,2) (<- note that the 2 latest numbers are
independent of the first 5 and can be equal to the first 5
numbers!!!).
The function that returns an array of N random numbers all different
is structured as follows:
'******************************************************
'Purpose: produce array of random ints in a range
'Inputs: nStart - the smallest value in the range
' nEnd - the largest value in the range
'Returns: array of integers
'
'Revised: 14 June 2005 to avoid randomizing vArr()
'******************************************************
Public Function RandInt( _
Optional ByVal nStart As Long = 1&, _
Optional ByVal nEnd As Long = -2147483647) As Variant
'returns array of random longs
'J.E. McGimpsey http://www.mcgimpsey.com/excel/randint.html
Dim vArr As Variant
Dim vResult As Variant
Dim nCount As Long
Dim nIndex As Long
Dim nRand As Long
Dim nRows As Long
Dim nCols As Long
Dim i As Long
Dim j As Long
Application.Volatile
If Not TypeOf Application.Caller Is Range Then Exit Function
With Application.Caller
nCount = .Count
If nEnd < nStart Then nEnd = nStart + nCount - 1
If nCount = 1 Then
RandInt = CLng((nEnd - nStart) * Rnd() + nStart)
ElseIf nCount > nEnd - nStart + 1 Then
RandInt = CVErr(xlErrNum)
Else
nRows = .Rows.Count
nCols = .Columns.Count
nIndex = nEnd - nStart + 1
ReDim vResult(1 To nRows, 1 To nCols)
ReDim vArr(0 To nIndex - 1)
For i = 0 To UBound(vArr)
vArr(i) = i + nStart
Next i
For i = 1 To nRows
For j = 1 To nCols
nRand = Int(Rnd() * nIndex)
nIndex = nIndex - 1
vResult(i, j) = vArr(nRand)
vArr(nRand) = vArr(nIndex)
Next j
Next i
RandInt = vResult
End If
End With
End Function
----------------------------------------------------------------------------------------
+
The structure that starts the cycle of VBA macro (DO. .. LOOP) is
associated with an object of type "CommandButton" (Button control),
the
Event "Click" and has the following schedule:
Private Sub cmdIniciar_Click()
Dim tryes As Variant
Application.ScreenUpdating = False
Sheets("Folha1").Select
Do
tryes = tryes + 1
Sheets("Folha1").Range("B16").Value = tryes
If Sheets("Folha1").Range("B20").Value = True Then
Application.ScreenUpdating = True
DoEvents
End
End If
If tryes Mod 1000 = 0 Then
Application.ScreenUpdating = True
DoEvents
End If
Application.ScreenUpdating = False
Loop
End Sub
--------------------------------------------------------------------------
+
What she does is repeat N times the interaction of the loop until the
value
the cell B20 is a logical value "TRUE" (and updates the screen after
1000 interactions).
Forgive me for my ignorance but the problem seems to me complex,
any assistance in helping me would be welcome!
Thank you for providing me the attention,
Ricardo Machado
p.s: sorry for the bad English but it was translated by an automatic
translations system. I hope that you can understand it...if not sorry :
(
numbers
AND DO NOT FULLY single repeatable in Excel 2003?
Well I found a function that produces random numbers different
each other. The only problem is that since they need to apply
function repeatedly through a cycle of VBA macro (DO. ... LOOP) it
(a function that returns random numbers different from each other)
combinations can produce duplicate (eg 1,2,3,4,5) (, (7, 10, 11, 4),
[1.2.3.4.5) ...<-- COMBINATION DUPLICATE!) And I would like to know
how to do that is avoid this and produze
randomly all the different possible combinations, knowing that
each combination is comprised of 5 different random numbers from 1
at 50 followed by 2 different random numbers 1 through 9 but
independent of the previous (eg 46,47,48,49,50,8,9 (); other
example: 1,2,3,4,5,1,2) (<- note that the 2 latest numbers are
independent of the first 5 and can be equal to the first 5
numbers!!!).
The function that returns an array of N random numbers all different
is structured as follows:
'******************************************************
'Purpose: produce array of random ints in a range
'Inputs: nStart - the smallest value in the range
' nEnd - the largest value in the range
'Returns: array of integers
'
'Revised: 14 June 2005 to avoid randomizing vArr()
'******************************************************
Public Function RandInt( _
Optional ByVal nStart As Long = 1&, _
Optional ByVal nEnd As Long = -2147483647) As Variant
'returns array of random longs
'J.E. McGimpsey http://www.mcgimpsey.com/excel/randint.html
Dim vArr As Variant
Dim vResult As Variant
Dim nCount As Long
Dim nIndex As Long
Dim nRand As Long
Dim nRows As Long
Dim nCols As Long
Dim i As Long
Dim j As Long
Application.Volatile
If Not TypeOf Application.Caller Is Range Then Exit Function
With Application.Caller
nCount = .Count
If nEnd < nStart Then nEnd = nStart + nCount - 1
If nCount = 1 Then
RandInt = CLng((nEnd - nStart) * Rnd() + nStart)
ElseIf nCount > nEnd - nStart + 1 Then
RandInt = CVErr(xlErrNum)
Else
nRows = .Rows.Count
nCols = .Columns.Count
nIndex = nEnd - nStart + 1
ReDim vResult(1 To nRows, 1 To nCols)
ReDim vArr(0 To nIndex - 1)
For i = 0 To UBound(vArr)
vArr(i) = i + nStart
Next i
For i = 1 To nRows
For j = 1 To nCols
nRand = Int(Rnd() * nIndex)
nIndex = nIndex - 1
vResult(i, j) = vArr(nRand)
vArr(nRand) = vArr(nIndex)
Next j
Next i
RandInt = vResult
End If
End With
End Function
----------------------------------------------------------------------------------------
+
The structure that starts the cycle of VBA macro (DO. .. LOOP) is
associated with an object of type "CommandButton" (Button control),
the
Event "Click" and has the following schedule:
Private Sub cmdIniciar_Click()
Dim tryes As Variant
Application.ScreenUpdating = False
Sheets("Folha1").Select
Do
tryes = tryes + 1
Sheets("Folha1").Range("B16").Value = tryes
If Sheets("Folha1").Range("B20").Value = True Then
Application.ScreenUpdating = True
DoEvents
End
End If
If tryes Mod 1000 = 0 Then
Application.ScreenUpdating = True
DoEvents
End If
Application.ScreenUpdating = False
Loop
End Sub
--------------------------------------------------------------------------
+
What she does is repeat N times the interaction of the loop until the
value
the cell B20 is a logical value "TRUE" (and updates the screen after
1000 interactions).
Forgive me for my ignorance but the problem seems to me complex,
any assistance in helping me would be welcome!
Thank you for providing me the attention,
Ricardo Machado
p.s: sorry for the bad English but it was translated by an automatic
translations system. I hope that you can understand it...if not sorry :
(
))