Next Loop for Solver Macro

[Mona9254]

Hey I need help making a Next Loop to add to the following macro which uses solver.

Sub Macro7()
'
' Macro7 Macro
'

'
SolverOk SetCell:="$O$41", MaxMinVal:=1, ValueOf:="0", ByChange:="$G$41"
SolverAdd CellRef:="$H$41", Relation:=1, FormulaText:="$T$41"
SolverAdd CellRef:="$H$41", Relation:=3, FormulaText:="$U$41"
SolverAdd CellRef:="$Q$41", Relation:=3, FormulaText:="$V$41"
SolverAdd CellRef:="$K$41", Relation:=3, FormulaText:="$W$41"
SolverAdd CellRef:="$G$41", Relation:=1, FormulaText:="$X$41"
SolverOk SetCell:="$O$41", MaxMinVal:=1, ValueOf:="0", ByChange:="$G$41"
SolverSolve

End Sub

 

[shg]

Can you explain the layout of your sheet and the range over which the loop is to be iterated?

 

[Mona9254]

I want the loop to work from G43 to G234...using the relative cells from the solver example in that row

 

[Mona9254]

Im getting a Compile Error: For Without Next.... Can someone help me fix the problem??

Private Sub Worksheet_Activate()
Sub Solverloop()

Dim r As Long
For r = 45 To 234

SolverOk SetCell:="$O$" & r, MaxMinVal:=1, ValueOf:="0", ByChange:="$G$" & r
SolverAdd CellRef:="$H$" & r, Relation:=1, FormulaText:="$T$" & r
SolverAdd CellRef:="$H$" & r, Relation:=3, FormulaText:="$U$" & r
SolverAdd CellRef:="$G$" & r, Relation:=1, FormulaText:="$X$" & r
SolverAdd CellRef:="$K$" & r, Relation:=3, FormulaText:="$W$" & r
SolverAdd CellRef:="$Q$" & r, Relation:=3, FormulaText:="$V$" & r
SolverOk SetCell:="$O$" & r, MaxMinVal:=1, ValueOf:="0", ByChange:="$G$" & r
SolverSolve
End Sub

 

[tusharm]

You are on the right track.

Check Excel VBA for 'for' (w/o the quotes). Then, in the help list click on the 'For...Next statement' link.

Also, you should use a SolverReset to reset the solver parameters at the start of each loop.

Im getting a Compile Error: For Without Next.... Can someone help me fix the problem??

Private Sub Worksheet_Activate()
Sub Solverloop()

Dim r As Long
For r = 45 To 234

SolverOk SetCell:="$O$" & r, MaxMinVal:=1, ValueOf:="0", ByChange:="$G$" & r
SolverAdd CellRef:="$H$" & r, Relation:=1, FormulaText:="$T$" & r
SolverAdd CellRef:="$H$" & r, Relation:=3, FormulaText:="$U$" & r
SolverAdd CellRef:="$G$" & r, Relation:=1, FormulaText:="$X$" & r
SolverAdd CellRef:="$K$" & r, Relation:=3, FormulaText:="$W$" & r
SolverAdd CellRef:="$Q$" & r, Relation:=3, FormulaText:="$V$" & r
SolverOk SetCell:="$O$" & r, MaxMinVal:=1, ValueOf:="0", ByChange:="$G$" & r
SolverSolve
End Sub