Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating random numbers from a given range
I want to generate 4 random numbers from a range of 1 through to 24. Each
number has to be different from the other 3 though. Is there a way to do this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating random numbers from a given range
How about:
Sub pickum() v1 = randbetween(1, 6) v1 = v1 & Chr(10) & randbetween(7, 12) v1 = v1 & Chr(10) & randbetween(13, 18) v1 = v1 & Chr(10) & randbetween(19, 24) MsgBox v1 End Sub -- Gary''s Student - gsnu201001 "Darren" wrote: I want to generate 4 random numbers from a range of 1 through to 24. Each number has to be different from the other 3 though. Is there a way to do this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating random numbers from a given range
I wasn't sure where you wanted the random number values to be stored. I
assumed it was in a range so I put the values in A1:A4. Did you want them in an array? Option Explicit Sub ProduceRandomNumbers() Dim MyRange As Range Dim cell As Range Set MyRange = Range("A1:A4") RunAgain: For Each cell In MyRange cell.Value = Int((24 * Rnd) + 1) Next cell For Each cell In MyRange If WorksheetFunction.CountIf(MyRange, cell.Value) 1 Then GoTo RunAgain End If Next cell End Sub Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Darren" wrote: I want to generate 4 random numbers from a range of 1 through to 24. Each number has to be different from the other 3 though. Is there a way to do this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating random numbers from a given range
On Feb 2, 4:11*pm, Darren wrote:
I want to generate 4 random numbers from a range of 1 through to 24. Each number has to be different from the other 3 though. Is there a way to do this? RANDBETWEEN is in the Analysis ToolPak AddIn, install that first. n1 = RANDBETWEEN(1, 24) DO n2 = RANDBETWEEN(1, 24) WHILE n2 = n1 DO n3 = RANDBETWEEN(1, 24) WHILE n3 = n1 OR n3 = n2 DO n4 = RANDBETWEEN(1, 24) WHILE n4 = n1 OR n4 = n2 OR n4 = n3 Phil Hibbs. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating random numbers from a given range
Thankyou for the prompt reply. Now that I have my random list of 4, I want to
find these values from within cells D1:AA1 and copy the corresponding lines of data contained in *7:*106 below the randomly generated sequence, which subsequently now goes horizontally. "Gary''s Student" wrote: How about: Sub pickum() v1 = randbetween(1, 6) v1 = v1 & Chr(10) & randbetween(7, 12) v1 = v1 & Chr(10) & randbetween(13, 18) v1 = v1 & Chr(10) & randbetween(19, 24) MsgBox v1 End Sub -- Gary''s Student - gsnu201001 "Darren" wrote: I want to generate 4 random numbers from a range of 1 through to 24. Each number has to be different from the other 3 though. Is there a way to do this? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating random numbers from a given range
Sorry, those should be LOOP WHILE ...
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating random numbers from a given range
Darren wrote:
Thankyou for the prompt reply. Now that I have my random list of 4, I want to find these values from within cells D1:AA1 and copy the corresponding lines of data contained in *7:*106 below the randomly generated sequence, which subsequently now goes horizontally. I don't understand. Phil Hibbs. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating random numbers from a given range
Another way -
In A1:24 enter the formula =RAND() Name A1:A24 "Rands" In B1 =MATCH(SMALL(Rands,1),Rands,0) in B2:B4 similar formula but change the 1 to 2, 3 & 4 respectively If you don't want new random numbers on each re-calc, put the rand() formulas elsewhere and copy to A1:A24 (or the named range) as values when you want a new set of unique random numbers. Regards, Peter T "Darren" wrote in message ... I want to generate 4 random numbers from a range of 1 through to 24. Each number has to be different from the other 3 though. Is there a way to do this? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating random numbers from a given range
This is untested, but give it a shot
Sub ProduceRandomNumbers() Dim i As Long Dim MyNumber As Long Dim MyNumbers(0 To 3) As Long Dim n As Long Dim FoundRange As Range Dim c As Long Dim MyRange As Range RunAgain: ' assign random numbers to array For i = 0 To 3 MyNumbers(i) = Int((24 * Rnd) + 1) Next i ' check for duplicates For i = 0 To 3 n = 0 MyNumber = MyNumbers(i) If MyNumber = MyNumbers(i) Then n = n + 1 If n 1 Then GoTo RunAgain End If Next i ' find number in header For i = 0 To 3 Set FoundRange = Range("D1:AA1").Find(What:=MyNumbers(i)) If Not FoundRange Is Nothing Then c = FoundRange.Column Set MyRange = Union(Range(Cells(7, c), Cells(106, c)), MyRange) End If Next i ' copy range to new sheet Sheets("Sheet2").Range("A1") = MyRange End Sub -- Cheers, Ryan "Darren" wrote: Thankyou for the prompt reply. Now that I have my random list of 4, I want to find these values from within cells D1:AA1 and copy the corresponding lines of data contained in *7:*106 below the randomly generated sequence, which subsequently now goes horizontally. "Gary''s Student" wrote: How about: Sub pickum() v1 = randbetween(1, 6) v1 = v1 & Chr(10) & randbetween(7, 12) v1 = v1 & Chr(10) & randbetween(13, 18) v1 = v1 & Chr(10) & randbetween(19, 24) MsgBox v1 End Sub -- Gary''s Student - gsnu201001 "Darren" wrote: I want to generate 4 random numbers from a range of 1 through to 24. Each number has to be different from the other 3 though. Is there a way to do this? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating random numbers from a given range
Hello Darren,
I suggest to use my UDF UniqRandInt: http://sulprobil.com/html/uniqrandint.html Regards, Bernd |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating random numbers from a given range
Thankyou Ryan, but this is heading away from where I want to go. I have since
replaced the 4 random numbers with 4 Skills from a list of 24. My actual chart has a list of names in A7:A106. The skills are in cells D1:AA1. I want to find the generated skill name from within D1:AA1, lets say for example the 1st random skill corresponds to the skill in H1. I now want to copy The values from H7:H106 under the 1st randomly generated skill. Same for the 2nd, 3rd and 4th. I hope I made this clearer. "Ryan H" wrote: This is untested, but give it a shot Sub ProduceRandomNumbers() Dim i As Long Dim MyNumber As Long Dim MyNumbers(0 To 3) As Long Dim n As Long Dim FoundRange As Range Dim c As Long Dim MyRange As Range RunAgain: ' assign random numbers to array For i = 0 To 3 MyNumbers(i) = Int((24 * Rnd) + 1) Next i ' check for duplicates For i = 0 To 3 n = 0 MyNumber = MyNumbers(i) If MyNumber = MyNumbers(i) Then n = n + 1 If n 1 Then GoTo RunAgain End If Next i ' find number in header For i = 0 To 3 Set FoundRange = Range("D1:AA1").Find(What:=MyNumbers(i)) If Not FoundRange Is Nothing Then c = FoundRange.Column Set MyRange = Union(Range(Cells(7, c), Cells(106, c)), MyRange) End If Next i ' copy range to new sheet Sheets("Sheet2").Range("A1") = MyRange End Sub -- Cheers, Ryan "Darren" wrote: Thankyou for the prompt reply. Now that I have my random list of 4, I want to find these values from within cells D1:AA1 and copy the corresponding lines of data contained in *7:*106 below the randomly generated sequence, which subsequently now goes horizontally. "Gary''s Student" wrote: How about: Sub pickum() v1 = randbetween(1, 6) v1 = v1 & Chr(10) & randbetween(7, 12) v1 = v1 & Chr(10) & randbetween(13, 18) v1 = v1 & Chr(10) & randbetween(19, 24) MsgBox v1 End Sub -- Gary''s Student - gsnu201001 "Darren" wrote: I want to generate 4 random numbers from a range of 1 through to 24. Each number has to be different from the other 3 though. Is there a way to do this? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating random numbers from a given range
Thankyou all for your help. Unfortunately we are heading in the wrong
direction. I have my randon list which is now text not numbers. Taking only the 1st random answer, this is what I want to do: My chart has names in A7:A78, skills in D1:AA1. Using RAND() and indexing a separate vertical list of the skills I can randomly generate 4 from the list. These are placed in B160:E160 with the list of names copied to A161:A232. I want to find the 1st randomly generated skill (B160) from within D1:AA1 then copy the corresponding data from below it (*7:*78) into B161:B232. For example: 1st generated skill is archery. I want to find archery in D1:AA1 which is actually H1 then copy H7:H78 into B161:B232. How would I go about this? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating random numbers from a given range
Figured it out.
in cell C161 I have =INDEX($D7:$AA7,MATCH(C$160,$D$1:$AA$1,0)) then copy it across 4 columns and down to C232:F232 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
generating random numbers without repeats | Excel Discussion (Misc queries) | |||
Generating random numbers | Excel Worksheet Functions | |||
Generating Random numbers. | Excel Discussion (Misc queries) | |||
generating non-repeating random numbers in a range | Excel Programming | |||
Generating Random Number from a set of numbers | Excel Discussion (Misc queries) |