Sub GolfOpt()
Application.ScreenUpdating = False
Dim n, k, z As Integer
Dim m, BinCoeff, MaxCap, Ctr, Cost, RowCtr As Long
Dim MyRoster() As Variant
Dim G1, G2, G3, G4, G5, G6 As Variant
k = 6
MaxCap = 50000
n = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
BinCoeff = Application.WorksheetFunction.Combin(n, k)
ReDim MyRoster(1 To BinCoeff, 1 To k + 1)
Ctr = 1
For G1 = 1 To n - 5
For G2 = G1 + 1 To n - 4
For G3 = G2 + 1 To n - 3
For G4 = G3 + 1 To n - 2
For G5 = G4 + 1 To n - 1
For G6 = G5 + 1 To n
Cost = 0
MyRoster(Ctr, 1) = G1
Cost = Cost + Sheets(1).Cells(G1, 2)
MyRoster(Ctr, 2) = G2
Cost = Cost + Sheets(1).Cells(G2, 2)
MyRoster(Ctr, 3) = G3
Cost = Cost + Sheets(1).Cells(G3, 2)
MyRoster(Ctr, 4) = G4
Cost = Cost + Sheets(1).Cells(G4, 2)
MyRoster(Ctr, 5) = G5
Cost = Cost + Sheets(1).Cells(G5, 2)
MyRoster(Ctr, 6) = G6
Cost = Cost + Sheets(1).Cells(G6, 2)
MyRoster(Ctr, 7) = Cost
Ctr = Ctr + 1
Next G6
Next G5
Next G4
Next G3
Next G2
Next G1
Sheets(2).Cells.Clear
Sheets(2).Cells(1, 1) = "Cost"
Sheets(2).Cells(1, 1).Font.Bold = True
For z = 1 To 6
Sheets(2).Cells(1, z + 1) = "Player " & z
Sheets(2).Cells(1, z + 1).Font.Bold = True
Next z
RowCtr = 2
For m = 1 To BinCoeff
If MyRoster(m, 7) <= MaxCap Then
Sheets(2).Cells(RowCtr, 1) = MyRoster(m, 7)
For z = 1 To 6
Sheets(2).Cells(RowCtr, z + 1) = Sheets(1).Cells(MyRoster(m, z), 1)
Next z
RowCtr = RowCtr + 1
End If
Next m
Sheets(2).Activate
Sheets(2).Columns("A:A").Select
Sheets(2).Sort.SortFields.Clear
Sheets(2).Sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortTextAsNumbers
With Sheets(2).Sort
.SetRange Sheets(2).UsedRange
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.ScreenUpdating = True
End Sub
when you fill column "A" with names and column "B" with price, it will create the permutations on sheet 2. My goal by modifying this, is so i could find the permutations for 2, 3, and 4, variables as opposed to 6 like it shows in the code listed above.
Bookmarks