Sub SolverEachHour()
'State variables
Dim cellchange As Range 'celdas cambiantes
Dim cellGoal As Range ' celda función objetivo
Dim cellendtime As Range
Dim cellvb1 As Range
Dim cellvb2 As Range
Dim cellPdg1 As Range
Dim cellPdg2 As Range
Dim cellPmin1 As Range
Dim cellPmax1 As Range
Dim cellPmin2 As Range
Dim cellPmax2 As Range
Dim cellsum As Range
Dim celldem As Range
Dim cellcharge As Range
Dim cellsoc As Range
Dim cellfisrt As Range
Dim cellsecond As Range
'start variables
Set cellchange = Sheets("optimization").Range("G4:L4")
Set cellGoal = Sheets("optimization").Range("AK4")
Set cellendtime = Sheets("optimization").Range("B4")
Set cellvb1 = Sheets("optimization").Range("k4")
Set cellvb2 = Sheets("optimization").Range("l4")
Set cellPdg1 = Sheets("optimization").Range("G4")
Set cellPdg2 = Sheets("optimization").Range("H4")
Set cellPmin1 = Sheets("optimization").Range("x4")
Set cellPmax1 = Sheets("optimization").Range("z4")
Set cellPmin2 = Sheets("optimization").Range("y4")
Set cellPmax2 = Sheets("optimization").Range("aa4")
Set cellsum = Sheets("optimization").Range("w4")
Set celldem = Sheets("optimization").Range("v4")
Set cellcharge = Sheets("optimization").Range("q4")
Set cellsoc = Sheets("optimization").Range("s4")
Set cellfirst = Sheets("optimization").Range("ai4")
Set cellsecond = Sheets("optimization").Range("aj4")
'Optimization with SOLVER
Do
solverreset
solverok setcell:=cellGoal.Address(True, True), maxminval:=2, bychange:=cellchange.Address(True, True), engine:=2 'llamar a solver, función objetivo y celdas cambiantes
'restricciones (3 >=; 1<=)
solveradd cellref:=cellvb1.Address(True, True), relation:=5
solveradd cellref:=cellvb2.Address(True, True), relation:=5
solveradd cellref:=cellPdg1.Address(True, True), relation:=3, formulatext:=cellPmin1.Address(True, True)
solveradd cellref:=cellPdg1.Address(True, True), relation:=1, formulatext:=cellPmax1.Address(True, True)
solveradd cellref:=cellPdg2.Address(True, True), relation:=3, formulatext:=cellPmin2.Address(True, True)
solveradd cellref:=cellPdg2.Address(True, True), relation:=1, formulatext:=cellPmax2.Address(True, True)
solveradd cellref:=cellsum.Address(True, True), relation:=3, formulatext:=celldem.Address(True, True)
solveradd cellref:=cellcharge.Address(True, True), relation:=3, formulatext:=240
solveradd cellref:=cellsoc.Address(True, True), relation:=1, formulatext:=1
solveradd cellref:=cellfirst.Address(True, True), relation:=3, formulatext:=cellsecond.Address(True, True)
solversolve userfinish:=True
solverfinish keepfinal:=1
'Next variables
Set cellchange = cellchange.Offset(1, 0)
Set cellGoal = cellGoal.Offset(1, 0)
Set cellendtime = cellendtime.Offset(1, 0)
Set cellvb1 = cellvb1.Offset(1, 0)
Set cellvb2 = cellvb2.Offset(1, 0)
Set cellPdg1 = cellPdg1.Offset(1, 0)
Set cellPdg2 = cellPdg2.Offset(1, 0)
Set cellPmin1 = cellPmin1.Offset(1, 0)
Set cellPmax1 = cellPmax1.Offset(1, 0)
Set cellPmin2 = cellPmin2.Offset(1, 0)
Set cellPmax2 = cellPmax2.Offset(1, 0)
Set cellsum = cellsum.Offset(1, 0)
Set celldem = celldem.Offset(1, 0)
Set cellcharge = cellcharge.Offset(1, 0)
Set cellsoc = cellsoc.Offset(1, 0)
Set cellfirst = cellfirst.Offset(1, 0)
Set cellsecond = cellsecond.Offset(1, 0)
Loop While Trim(cellendtime.Text) <> "" 'until end time cell is empty
End Sub
Any comments would be much appreciated! I'm new in the forum and actually only been working with macros for a week but I will try to contribute to it when I have some time.
Bookmarks