Hi everyone!

I am writing a macro in which I need to solve a small linear optimization problem with binary variables many times. Each row of the spreadsheet corresponds to one hour of the year, for each hour (row) a small (7 variables) MIP problem has to be solved. Once the problem has been solved for one row, the problem in the next row takes some values of the solution of the problem in the row above it, but each time the solver is called (one for each row) it only uses data from that particular row. Ideally, I would need to solve the spreadsheet for every hour of the year (8760 times), but when I try a loop for 30 days (720 solves) it already takes a long time to solve.

My question then is, could anyone give me a hint on what would be the most efficient way to loop the solve statement so that it takes the least possible amount of time? The problem for each row is solved inmediately but I find that with the code I have now, solving let's say a 100 problems does not take 100 times the time it takes to solve one problem, but much longer, it probably has something to do with memory and computational stuff I'm no expert about. I think there must be a way in which solving time depends linearly on the amount of problems solved but I haven't found it, any ideas?

Here is the code I'm using:

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.

Many thanks in advance!