Note: This was originally posted at https://www.excelforum.com/excel-programming-vba-macros/1274124-help-with-arrays.html
I have not been able to get any responses to addressing this.
My apologies if this doesn't make sense, I am new to using arrays and have been trying to research this but I am quite lost. Please let me know if this does not make sense.
I have the current solver below that I want to update variables on. The first section works correctly for a single solve but I would like it to solve numerous times to get the 2nd, 3rd etc best results. I also want to limit the number of times each value is used, for example in the sample data below, Gary can be used a maximum of 5 times, James 2 times etc. Each can only be used once per solve. When they have been used to their max, they would not be available for any successive solves. I have been told the best way to do this is to use an array and after each solve, reduce the number by one for the values used.
The second issue I am having is reducing the max solved value through each loop, I am assuming the solved for value should also go into an array so that it can be changed as well.
Lastly, for the results, I have been researching and it appears that it would be best to store the results in an array until complete then to add to my spreadsheet instead of each solve.
Can someone provide any insight on how I should approach this?
<code style="font-family: monospace; font-style: normal; font-weight: 400; line-height: 12px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">Option Explicit
Sub TestSolve()
'Reset Solver
SolverReset
'Create New Spreadsheet - Named "Export"
Worksheets.Add after:=Sheets("Parameters")
ActiveSheet.Name = "Export"
Worksheets("Parameters").Activate
'Loop - Number of times to run - 1 to EndNumber
Dim StartNumber As Long, EndNumber As Long, i As Long
StartNumber = 1
EndNumber = Range("L11").Value
For i = StartNumber To EndNumber
'Set Constraints
SolverOk SetCell:="$L$4", MaxMinVal:=1, ValueOf:=0, ByChange:="$A$2:$A$134", _
Engine:=1, EngineDesc:="GRG Nonlinear"
Solveradd CellRef:="$A$2:$A$134", Relation:=5, FormulaText:="binary"
Solveradd CellRef:="$L$10", Relation:=1, FormulaText:="=L$5"
Solveradd CellRef:="$L$10", Relation:=3, FormulaText:="=$L$6"
Solveradd CellRef:="$L$9", Relation:=2, FormulaText:="=$L$8"
Dim List_Current_row As Long
List_Current_row = Worksheets("Export").Cells(Rows.Count, 1).End(xlUp).Row + 1
If i = 0 Then
Worksheets("Export").Cells(List_Current_row, 1).x.Resize(1, 6) = WorksheetFunction.Transpose(Worksheets("Parameters").Range("C2:C201"))
Else
Worksheets("Export").Cells(List_Current_row, 1).x.Resize(200, 1) = WorksheetFunction.Transpose(Worksheets("Parameters").Range("C2:C201"))
End If
SolverSolve userfinish:=True
Next i
End Sub</code>
I have been able to do this manually but it takes a long time to run multiple times. I have a sample set of data I can provide if it would be helpful.I have not been able to get any responses to addressing this.
My apologies if this doesn't make sense, I am new to using arrays and have been trying to research this but I am quite lost. Please let me know if this does not make sense.
I have the current solver below that I want to update variables on. The first section works correctly for a single solve but I would like it to solve numerous times to get the 2nd, 3rd etc best results. I also want to limit the number of times each value is used, for example in the sample data below, Gary can be used a maximum of 5 times, James 2 times etc. Each can only be used once per solve. When they have been used to their max, they would not be available for any successive solves. I have been told the best way to do this is to use an array and after each solve, reduce the number by one for the values used.
The second issue I am having is reducing the max solved value through each loop, I am assuming the solved for value should also go into an array so that it can be changed as well.
Lastly, for the results, I have been researching and it appears that it would be best to store the results in an array until complete then to add to my spreadsheet instead of each solve.
Can someone provide any insight on how I should approach this?
<code style="font-family: monospace; font-style: normal; font-weight: 400; line-height: 12px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">Option Explicit
Sub TestSolve()
'Reset Solver
SolverReset
'Create New Spreadsheet - Named "Export"
Worksheets.Add after:=Sheets("Parameters")
ActiveSheet.Name = "Export"
Worksheets("Parameters").Activate
'Loop - Number of times to run - 1 to EndNumber
Dim StartNumber As Long, EndNumber As Long, i As Long
StartNumber = 1
EndNumber = Range("L11").Value
For i = StartNumber To EndNumber
'Set Constraints
SolverOk SetCell:="$L$4", MaxMinVal:=1, ValueOf:=0, ByChange:="$A$2:$A$134", _
Engine:=1, EngineDesc:="GRG Nonlinear"
Solveradd CellRef:="$A$2:$A$134", Relation:=5, FormulaText:="binary"
Solveradd CellRef:="$L$10", Relation:=1, FormulaText:="=L$5"
Solveradd CellRef:="$L$10", Relation:=3, FormulaText:="=$L$6"
Solveradd CellRef:="$L$9", Relation:=2, FormulaText:="=$L$8"
Dim List_Current_row As Long
List_Current_row = Worksheets("Export").Cells(Rows.Count, 1).End(xlUp).Row + 1
If i = 0 Then
Worksheets("Export").Cells(List_Current_row, 1).x.Resize(1, 6) = WorksheetFunction.Transpose(Worksheets("Parameters").Range("C2:C201"))
Else
Worksheets("Export").Cells(List_Current_row, 1).x.Resize(200, 1) = WorksheetFunction.Transpose(Worksheets("Parameters").Range("C2:C201"))
End If
SolverSolve userfinish:=True
Next i
End Sub</code>
Please let me know if you have any questions or comments.
