Hello All,
I created a macro that used to work for a previous project. Now that I have adapted it to a new project, when I run the macro, Excel keeps setting up the problem and the macro does not start running. The code is apparently correct so I was wondering if the problem is:
1. The problem is too complicated for Excel to solve it.
2. Some of the constraints could be conflicting with one another (I have checked and I have not found any inconsistencies)
I am using Excel 2013. I include first the code that does not run and then also the one that worked in case that helps. Thank you in advance for your help.
And this is the code that run properly:
I created a macro that used to work for a previous project. Now that I have adapted it to a new project, when I run the macro, Excel keeps setting up the problem and the macro does not start running. The code is apparently correct so I was wondering if the problem is:
1. The problem is too complicated for Excel to solve it.
2. Some of the constraints could be conflicting with one another (I have checked and I have not found any inconsistencies)
I am using Excel 2013. I include first the code that does not run and then also the one that worked in case that helps. Thank you in advance for your help.
Code:
For i = 11 To 40
SolverOk SetCell:="$SC$" & i, MaxMinVal:=2, ValueOf:=0, ByChange:="$EI$" & i & ",$EJ$" & i & ",$EK$" & i & ",$EL$" & i & ",$EM$" & i & ",$EN$" & i & ",$EO$" & i & ",$EP$" & i & ",$EQ$" & i & ",$ER$" & i & ",$ES$" & i & ",$ET$" & i & ",$EU$" & i & ",$EV$" & i & ",$EW$" & i & ",$EX$" & i & ",$EY$" & i & ",$EZ$" & i & ",$FA$" & i & ",$FB$" & i & ",$FC$" & i & ",$FD$" & i & ",$FE$" & i & ",$FF$" & i & ",$FG$" & i & ",$FH$" & i & ",$FI$" & i & ",$FJ$" & i & ",$FK$" & i & ",$FL$" & i & ",$FM$" & i & ",$FN$" & i & ",$FO$" & i & ",$FP$" & i & ",$FQ$" & i & ",$FR$" & i & ",$FS$" & i & ",$FT$" & i & ",$FU$" & i & ",$FV$" & i & ",$FW$" & i & ",$FX$" & i & ",$FY$" & i & ",$FZ$" & i & ",$GA$" & i & ",$GB$" & i & ",$GC$" & i & ",$GD$" & i _
, Engine:=2, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$IA$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$IB$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$IC$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$ID$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$IE$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$IF$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$ABJ$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$ABK$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$ABL$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$ABM$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$ABN$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$ABO$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$EI$" & i, Relation:=1, FormulaText:="$WT$" & i
SolverAdd CellRef:="$EJ$" & i, Relation:=1, FormulaText:="$WU$" & i
SolverAdd CellRef:="$EM$" & i, Relation:=1, FormulaText:="$WX$" & i
SolverAdd CellRef:="$EN$" & i, Relation:=1, FormulaText:="$WY$" & i
SolverAdd CellRef:="$EQ$" & i, Relation:=1, FormulaText:="$XB$" & i
SolverAdd CellRef:="$ER$" & i, Relation:=1, FormulaText:="$XC$" & i
SolverAdd CellRef:="$EU$" & i, Relation:=1, FormulaText:="$XF$" & i
SolverAdd CellRef:="$EV$" & i, Relation:=1, FormulaText:="$XG$" & i
SolverAdd CellRef:="$EY$" & i, Relation:=1, FormulaText:="$XJ$" & i
SolverAdd CellRef:="$EZ$" & i, Relation:=1, FormulaText:="$XK$" & i
SolverAdd CellRef:="$FC$" & i, Relation:=1, FormulaText:="$XN$" & i
SolverAdd CellRef:="$FD$" & i, Relation:=1, FormulaText:="$XO$" & i
SolverAdd CellRef:="$FG$" & i, Relation:=1, FormulaText:="$AAL$" & i
SolverAdd CellRef:="$FH$" & i, Relation:=1, FormulaText:="$AAM$" & i
SolverAdd CellRef:="$FK$" & i, Relation:=1, FormulaText:="$AAP$" & i
SolverAdd CellRef:="$FL$" & i, Relation:=1, FormulaText:="$AAQ$" & i
SolverAdd CellRef:="$FO$" & i, Relation:=1, FormulaText:="$AAT$" & i
SolverAdd CellRef:="$FP$" & i, Relation:=1, FormulaText:="$AAU$" & i
SolverAdd CellRef:="$FS$" & i, Relation:=1, FormulaText:="$AAX$" & i
SolverAdd CellRef:="$FT$" & i, Relation:=1, FormulaText:="$AAY$" & i
SolverAdd CellRef:="$FW$" & i, Relation:=1, FormulaText:="$ABB$" & i
SolverAdd CellRef:="$FX$" & i, Relation:=1, FormulaText:="$ABC$" & i
SolverAdd CellRef:="$GA$" & i, Relation:=1, FormulaText:="$ABF$" & i
SolverAdd CellRef:="$GB$" & i, Relation:=1, FormulaText:="$ABG$" & i
SolverAdd CellRef:="$EI$" & i, Relation:=3, FormulaText:="$ACF$" & i
SolverAdd CellRef:="$EJ$" & i, Relation:=3, FormulaText:="$ACG$" & i
SolverAdd CellRef:="$EM$" & i, Relation:=3, FormulaText:="$ACJ$" & i
SolverAdd CellRef:="$EN$" & i, Relation:=3, FormulaText:="$ACK$" & i
SolverAdd CellRef:="$EQ$" & i, Relation:=3, FormulaText:="$ACN$" & i
SolverAdd CellRef:="$ER$" & i, Relation:=3, FormulaText:="$ACO$" & i
SolverAdd CellRef:="$EU$" & i, Relation:=3, FormulaText:="$ACR$" & i
SolverAdd CellRef:="$EV$" & i, Relation:=3, FormulaText:="$ACS$" & i
SolverAdd CellRef:="$EY$" & i, Relation:=3, FormulaText:="$ACV$" & i
SolverAdd CellRef:="$EZ$" & i, Relation:=3, FormulaText:="$ACW$" & i
SolverAdd CellRef:="$FC$" & i, Relation:=3, FormulaText:="$ACZ$" & i
SolverAdd CellRef:="$FD$" & i, Relation:=3, FormulaText:="$ADA$" & i
SolverAdd CellRef:="$FG$" & i, Relation:=3, FormulaText:="$ADD$" & i
SolverAdd CellRef:="$FH$" & i, Relation:=3, FormulaText:="$ADE$" & i
SolverAdd CellRef:="$FK$" & i, Relation:=3, FormulaText:="$ADH$" & i
SolverAdd CellRef:="$FL$" & i, Relation:=3, FormulaText:="$ADI$" & i
SolverAdd CellRef:="$FO$" & i, Relation:=3, FormulaText:="$ADL$" & i
SolverAdd CellRef:="$FP$" & i, Relation:=3, FormulaText:="$ADM$" & i
SolverAdd CellRef:="$FS$" & i, Relation:=3, FormulaText:="$ADP$" & i
SolverAdd CellRef:="$FT$" & i, Relation:=3, FormulaText:="$ADQ$" & i
SolverAdd CellRef:="$FW$" & i, Relation:=3, FormulaText:="$ADT$" & i
SolverAdd CellRef:="$FX$" & i, Relation:=3, FormulaText:="$ADU$" & i
SolverAdd CellRef:="$GA$" & i, Relation:=3, FormulaText:="$ADX$" & i
SolverAdd CellRef:="$GB$" & i, Relation:=3, FormulaText:="$ADY$" & i
SolverAdd CellRef:="$IG$" & i, Relation:=1, FormulaText:="$ACF$" & i
SolverAdd CellRef:="$IG$" & i, Relation:=1, FormulaText:="$ACF$" & i
SolverAdd CellRef:="$IG$" & i, Relation:=1, FormulaText:="$ACF$" & i
SolverAdd CellRef:="$IG$" & i, Relation:=1, FormulaText:="$ACF$" & i
SolverAdd CellRef:="$IG$" & i, Relation:=1, FormulaText:="$ACF$" & i
SolverAdd CellRef:="$IG$" & i, Relation:=1, FormulaText:="$ACF$" & i
SolverAdd CellRef:="$IG$" & i, Relation:=1, FormulaText:="$ACF$" & i
SolverAdd CellRef:="$IG$" & i, Relation:=1, FormulaText:="$ACF$" & i
SolverAdd CellRef:="$IG$" & i, Relation:=1, FormulaText:="$ACF$" & i
SolverAdd CellRef:="$IG$" & i, Relation:=1, FormulaText:="$ACF$" & i
SolverAdd CellRef:="$IG$" & i, Relation:=1, FormulaText:="$ACF$" & i
SolverAdd CellRef:="$IG$" & i, Relation:=1, FormulaText:="$ACF$" & i
SolverAdd CellRef:="$IG$" & i, Relation:=1, FormulaText:="$ACF$" & i
SolverAdd CellRef:="$IG$" & i, Relation:=1, FormulaText:="$ACF$" & i
SolverAdd CellRef:="$IG$" & i, Relation:=1, FormulaText:="$ACF$" & i
SolverAdd CellRef:="$IG$" & i, Relation:=1, FormulaText:="$ACF$" & i
SolverOk SetCell:="$SC$" & i, MaxMinVal:=2, ValueOf:=0, ByChange:="$EI$" & i & ",$EJ$" & i & ",$EK$" & i & ",$EL$" & i & ",$EM$" & i & ",$EN$" & i & ",$EO$" & i & ",$EP$" & i & ",$EQ$" & i & ",$ER$" & i & ",$ES$" & i & ",$ET$" & i & ",$EU$" & i & ",$EV$" & i & ",$EW$" & i & ",$EX$" & i & ",$EY$" & i & ",$EZ$" & i & ",$FA$" & i & ",$FB$" & i & ",$FC$" & i & ",$FD$" & i & ",$FE$" & i & ",$FF$" & i & ",$FG$" & i & ",$FH$" & i & ",$FI$" & i & ",$FJ$" & i & ",$FK$" & i & ",$FL$" & i & ",$FM$" & i & ",$FN$" & i & ",$FO$" & i & ",$FP$" & i & ",$FQ$" & i & ",$FR$" & i & ",$FS$" & i & ",$FT$" & i & ",$FU$" & i & ",$FV$" & i & ",$FW$" & i & ",$FX$" & i & ",$FY$" & i & ",$FZ$" & i & ",$GA$" & i & ",$GB$" & i & ",$GC$" & i & ",$GD$" & i _
, Engine:=2, EngineDesc:="GRG Nonlinear"
SolverSolve True
SolverReset
Next i
End SubAnd this is the code that run properly:
Code:
For i = 11 To 40
SolverMultistar = True
SolverOk SetCell:="$KS$" & i, MaxMinVal:=2, ValueOf:=0, ByChange:="$CG$" & i & ",$CI$" & i & ",$CK$" & i & ",$CM$" & i & ",$CO$" & i & ",$CQ$" & i & ",$CS$" & i & ",$CU$" & i & ",$CW$" & i & ",$CY$" & i & ",$DA$" & i & ",$DC$" & i & ",$CH$" & i & ",$CJ$" & i & ",$CL$" & i & ",$CN$" & i & ",$CP$" & i & ",$CR$" & i & ",$CT$" & i & ",$CV$" & i & ",$CX$" & i & ",$CZ$" & i & ",$DB$" & i & ",$DD$" & i _
, Engine:=2, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$CG$" & i, Relation:=1, FormulaText:="$NB$" & i
SolverAdd CellRef:="$CI$" & i, Relation:=1, FormulaText:="$ND$" & i
SolverAdd CellRef:="$CK$" & i, Relation:=1, FormulaText:="$NF$" & i
SolverAdd CellRef:="$CM$" & i, Relation:=1, FormulaText:="$NH$" & i
SolverAdd CellRef:="$CO$" & i, Relation:=1, FormulaText:="$NJ$" & i
SolverAdd CellRef:="$CQ$" & i, Relation:=1, FormulaText:="$NL$" & i
SolverAdd CellRef:="$CS$" & i, Relation:=1, FormulaText:="$OX$" & i
SolverAdd CellRef:="$CU$" & i, Relation:=1, FormulaText:="$OZ$" & i
SolverAdd CellRef:="$CW$" & i, Relation:=1, FormulaText:="$PB$" & i
SolverAdd CellRef:="$CY$" & i, Relation:=1, FormulaText:="$PD$" & i
SolverAdd CellRef:="$DA$" & i, Relation:=1, FormulaText:="$PF$" & i
SolverAdd CellRef:="$DC$" & i, Relation:=1, FormulaText:="$PH$" & i
SolverAdd CellRef:="$EI$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$EJ$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$EK$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$EL$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$EM$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$EN$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$PJ$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$PK$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$PL$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$PM$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$PN$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$PO$" & i, Relation:=2, FormulaText:="0"
SolverAdd CellRef:="$CG$" & i, Relation:=3, FormulaText:="$PZ$" & i
SolverAdd CellRef:="$CI$" & i, Relation:=3, FormulaText:="$QB$" & i
SolverAdd CellRef:="$CK$" & i, Relation:=3, FormulaText:="$QD$" & i
SolverAdd CellRef:="$CM$" & i, Relation:=3, FormulaText:="$QF$" & i
SolverAdd CellRef:="$CO$" & i, Relation:=3, FormulaText:="$QH$" & i
SolverAdd CellRef:="$CQ$" & i, Relation:=3, FormulaText:="$QJ$" & i
SolverAdd CellRef:="$CS$" & i, Relation:=3, FormulaText:="$QL$" & i
SolverAdd CellRef:="$CU$" & i, Relation:=3, FormulaText:="$QN$" & i
SolverAdd CellRef:="$CW$" & i, Relation:=3, FormulaText:="$QP$" & i
SolverAdd CellRef:="$CY$" & i, Relation:=3, FormulaText:="$QR$" & i
SolverAdd CellRef:="$DA$" & i, Relation:=3, FormulaText:="$OT$" & i
SolverAdd CellRef:="$DC$" & i, Relation:=3, FormulaText:="$QV$" & i
SolverAdd CellRef:="$EO$" & i, Relation:=1, FormulaText:="$PP$" & i
SolverAdd CellRef:="$EP$" & i, Relation:=1, FormulaText:="$PQ$" & i
SolverAdd CellRef:="$EQ$" & i, Relation:=1, FormulaText:="$PR$" & i
SolverAdd CellRef:="$ER$" & i, Relation:=1, FormulaText:="$PS$" & i
SolverAdd CellRef:="$ES$" & i, Relation:=1, FormulaText:="$PT$" & i
SolverAdd CellRef:="$ET$" & i, Relation:=1, FormulaText:="$PU$" & i
SolverAdd CellRef:="$EU$" & i, Relation:=1, FormulaText:="$PV$" & i
SolverAdd CellRef:="$EV$" & i, Relation:=1, FormulaText:="$PW$" & i
SolverAdd CellRef:="$EW$" & i, Relation:=1, FormulaText:="$PX$" & i
SolverAdd CellRef:="$EX$" & i, Relation:=1, FormulaText:="$PY$" & i
SolverOk SetCell:="$KS$" & i, MaxMinVal:=2, ValueOf:=0, ByChange:="$CG$" & i & ",$CI$" & i & ",$CK$" & i & ",$CM$" & i & ",$CO$" & i & ",$CQ$" & i & ",$CS$" & i & ",$CU$" & i & ",$CW$" & i & ",$CY$" & i & ",$DA$" & i & ",$DC$" & i & ",$CH$" & i & ",$CJ$" & i & ",$CL$" & i & ",$CN$" & i & ",$CP$" & i & ",$CR$" & i & ",$CT$" & i & ",$CV$" & i & ",$CX$" & i & ",$CZ$" & i & ",$DB$" & i & ",$DD$" & i _
, Engine:=2, EngineDesc:="GRG Nonlinear"
SolverOptions Assumenonneg:=True
SolverSolve True
SolverReset
Next i
End Sub
