Aqui está o meu código VBA:
Sub solver_macro()
SolverOk SetCell:="$H$1", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$2:$E$33", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$F$5", Relation:=2, FormulaText:="$G$5"
SolverAdd CellRef:="$F$9", Relation:=2, FormulaText:="$G$9"
SolverAdd CellRef:="$F$13", Relation:=2, FormulaText:="$G$13"
SolverAdd CellRef:="$F$17", Relation:=2, FormulaText:="$G$17"
SolverAdd CellRef:="$F$21", Relation:=2, FormulaText:="$G$21"
SolverAdd CellRef:="$F$25", Relation:=2, FormulaText:="$G$25"
SolverAdd CellRef:="$F$29", Relation:=2, FormulaText:="$G$29"
SolverAdd CellRef:="$F$33", Relation:=2, FormulaText:="$G$33"
SolverOk SetCell:="$H$1", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$2:$E$33", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$H$1", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$2:$E$33", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve
End Sub
Minha primeira tentativa:
Sub solver_macro()
Dim i As Integer
Dim PERIOD As Integer
PERIOD = 7
SolverOk SetCell:="$H$1", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$2:$E$33", _
Engine:=1, EngineDesc:="GRG Nonlinear"
For i = 0 To PERIOD
SolverAdd CellRef:="$F$5+4*i", Relation:=2, FormulaText:="$G$5+4*i"
Next i
SolverOk SetCell:="$H$1", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$2:$5+4*PERIOD", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$H$1", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$2:$5+4*PERIOD", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve
End Sub
Você poderia me ajudar a corrigir esse loop, por favor? Obrigado antecipadamente!
Edit: Obrigado pela resposta. Como posso fazer o mesmo se quiser fazer um loop com letras em vez de números?
Ainda o mesmo código, mas os dados são horizontais. Portanto, o loop passa pelas letras (colunas) em vez de números (linhas):
SolverAdd CellRef:="$G$88", Relation:=2, FormulaText:="$G$89"
SolverAdd CellRef:="$K$88", Relation:=2, FormulaText:="$K$89"
SolverAdd CellRef:="$O$88", Relation:=2, FormulaText:="$O$89"
SolverAdd CellRef:="$S$88", Relation:=2, FormulaText:="$S$89"
SolverAdd CellRef:="$W$88", Relation:=2, FormulaText:="$W$89"
SolverAdd CellRef:="$AA$88", Relation:=2, FormulaText:="$AA$89"