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?

0 |

2/2/2010 4:11:01 PM

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?

0 |

2/2/2010 4:35:01 PM

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?

0 |

2/2/2010 4:58:11 PM

On Feb 2, 4:11=A0pm, Darren <Dar...@discussions.microsoft.com> 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 =3D RANDBETWEEN(1, 24) DO n2 =3D RANDBETWEEN(1, 24) WHILE n2 =3D n1 DO n3 =3D RANDBETWEEN(1, 24) WHILE n3 =3D n1 OR n3 =3D n2 DO n4 =3D RANDBETWEEN(1, 24) WHILE n4 =3D n1 OR n4 =3D n2 OR n4 =3D n3 Phil Hibbs.

0 |

2/2/2010 4:59:29 PM

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?

0 |

2/2/2010 5:01:01 PM

Sorry, those should be LOOP WHILE ...

0 |

2/2/2010 5:13:42 PM

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.

0 |

2/2/2010 5:15:21 PM

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" <Darren@discussions.microsoft.com> wrote in message news:61F10F6B-4AB4-45ED-9BFF-8ED2490F5204@microsoft.com... >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?

0 |

2/2/2010 5:18:46 PM

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?

0 |

2/2/2010 5:29:01 PM

Hello Darren, I suggest to use my UDF UniqRandInt: http://sulprobil.com/html/uniqrandint.html Regards, Bernd

0 |

2/2/2010 5:39:15 PM

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?

0 |

2/2/2010 5:43:03 PM

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?

0 |

2/2/2010 6:43:01 PM

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

0 |

2/2/2010 7:18:10 PM