Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling random rows
Hi Group,
I use code (attached below) to randomise 381 rows, which then allows Solver to produce a random fantasy football team. It works very well but I am looking to add a 'feature' to my Solver routine so that is produces a team with 'preferred' players. I've tried working out a solution on my own but I come against Solver's 200 changing cells limit and was looking for some expert help or advice. Here's my problem...let's say I want a player called Van Nistelrooy (Man U fan BTW) in my team. After the random code kicks in, if Van Nistelrooy is not randomised into the top 200 rows (Solver's limit) Solver cannot produce a team with my preferred player. Is it possible to add code after the random code to check the cells (Y2:Y6) of my preferred players and, if there are preferred players, somehow check my rows (A6:E381) to find these players and cut and paste these to the top of my list. That way, Solver will produce a team with my preferred players in as they will be in the top 200 rows. (ColA contains the name of each player.) Will be glad of any help Tony 'Random code start Public Sub shuffle() Dim RndCol(380, 0) Dim i Randomize Application.ScreenUpdating = False ActiveSheet.Columns(8).Insert For i = 0 To UBound(RndCol, 1) RndCol(i, 0) = Rnd Next i Range(Cells(6, 8), Cells(381, 8)) = RndCol ActiveSheet.Range("a6:h381").Sort _ Key1:=ActiveSheet.Columns("H") ActiveSheet.Columns(8).Delete Application.ScreenUpdating = True End Sub 'Random code end |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling random rows
Assume the player names are in column A and the matching perferred names are
in Y2:Y6 as you state. When you insert Column H, then Y2:Y6 will be Z2:Z6 'Random code start Public Sub shuffle() Application.ScreenUpdating = False ActiveSheet.Columns(8).Insert Range(Cells(6, 8), Cells(381, 8)).Formula = "=if(iserror(match(A6,$Z$2:$Z$6,0)),rand(),1.1 )" ActiveSheet.Range("a6:h381").Sort _ Key1:=ActiveSheet.Columns("H") ActiveSheet.Columns(8).Delete Application.ScreenUpdating = True End Sub Should do what you want. -- Regards, Tom Ogilvy "Tony Scullion" wrote in message ... Hi Group, I use code (attached below) to randomise 381 rows, which then allows Solver to produce a random fantasy football team. It works very well but I am looking to add a 'feature' to my Solver routine so that is produces a team with 'preferred' players. I've tried working out a solution on my own but I come against Solver's 200 changing cells limit and was looking for some expert help or advice. Here's my problem...let's say I want a player called Van Nistelrooy (Man U fan BTW) in my team. After the random code kicks in, if Van Nistelrooy is not randomised into the top 200 rows (Solver's limit) Solver cannot produce a team with my preferred player. Is it possible to add code after the random code to check the cells (Y2:Y6) of my preferred players and, if there are preferred players, somehow check my rows (A6:E381) to find these players and cut and paste these to the top of my list. That way, Solver will produce a team with my preferred players in as they will be in the top 200 rows. (ColA contains the name of each player.) Will be glad of any help Tony 'Random code start Public Sub shuffle() Dim RndCol(380, 0) Dim i Randomize Application.ScreenUpdating = False ActiveSheet.Columns(8).Insert For i = 0 To UBound(RndCol, 1) RndCol(i, 0) = Rnd Next i Range(Cells(6, 8), Cells(381, 8)) = RndCol ActiveSheet.Range("a6:h381").Sort _ Key1:=ActiveSheet.Columns("H") ActiveSheet.Columns(8).Delete Application.ScreenUpdating = True End Sub 'Random code end |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling random rows
Tom,
Ingenious code - worked a treat. Many thanks Tony -----Original Message----- Assume the player names are in column A and the matching perferred names are in Y2:Y6 as you state. When you insert Column H, then Y2:Y6 will be Z2:Z6 'Random code start Public Sub shuffle() Application.ScreenUpdating = False ActiveSheet.Columns(8).Insert Range(Cells(6, 8), Cells(381, 8)).Formula = "=if(iserror(match(A6,$Z$2:$Z$6,0)),rand(),1.1 )" ActiveSheet.Range("a6:h381").Sort _ Key1:=ActiveSheet.Columns("H") ActiveSheet.Columns(8).Delete Application.ScreenUpdating = True End Sub Should do what you want. -- Regards, Tom Ogilvy "Tony Scullion" wrote in message ... Hi Group, I use code (attached below) to randomise 381 rows, which then allows Solver to produce a random fantasy football team. It works very well but I am looking to add a 'feature' to my Solver routine so that is produces a team with 'preferred' players. I've tried working out a solution on my own but I come against Solver's 200 changing cells limit and was looking for some expert help or advice. Here's my problem...let's say I want a player called Van Nistelrooy (Man U fan BTW) in my team. After the random code kicks in, if Van Nistelrooy is not randomised into the top 200 rows (Solver's limit) Solver cannot produce a team with my preferred player. Is it possible to add code after the random code to check the cells (Y2:Y6) of my preferred players and, if there are preferred players, somehow check my rows (A6:E381) to find these players and cut and paste these to the top of my list. That way, Solver will produce a team with my preferred players in as they will be in the top 200 rows. (ColA contains the name of each player.) Will be glad of any help Tony 'Random code start Public Sub shuffle() Dim RndCol(380, 0) Dim i Randomize Application.ScreenUpdating = False ActiveSheet.Columns(8).Insert For i = 0 To UBound(RndCol, 1) RndCol(i, 0) = Rnd Next i Range(Cells(6, 8), Cells(381, 8)) = RndCol ActiveSheet.Range("a6:h381").Sort _ Key1:=ActiveSheet.Columns("H") ActiveSheet.Columns(8).Delete Application.ScreenUpdating = True End Sub 'Random code end . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table - Controlling how many rows of data are displayed in t | Excel Discussion (Misc queries) | |||
How to Set Range to 50 Random nonsequencial rows? | Excel Discussion (Misc queries) | |||
Random Sampling rows | Excel Discussion (Misc queries) | |||
How can I make the rows go into a random order? | Excel Worksheet Functions | |||
Controlling odd even rows when pasting data | Excel Discussion (Misc queries) |