Controlling random rows

[Tony Scullion]

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

 

[Tom Ogilvy]

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.

 

[Tony Scullion]

Tom,

Ingenious code - worked a treat.

Many thanks

Tony