Help with Solver & VBA ?
 

I'm trying to loop through rows of data and I'm having
trouble with the cell addresses. Microsoft's Solver
discussion only utilizes specific cell addresses and I'm
trying to change the target cell and constraint cells on a
row-by-row basis. CLearly I'm not addressing the cells
correctly by I'm not sure how to do it. Any help is
greatly appreciated. Thanks!


Here's the code:

Dim i As Integer ' Number of rows
Dim j As Integer ' Start row

i = Cells(11, 3)
For j = 14 To i

SolverOk SetCell:=Range("Cells(j, 14)"), MaxMinVal:=2,
ByChange:=Range("Cells(j,7):Cells(j,9)")
SolverAdd CellRef:=Range("Cells(j, 3)"), Relation:=1,
FormulaText:="Cells(j, 11)"
SolverAdd CellRef:=Range("Cells(j, 4)"), Relation:=1,
FormulaText:="Cells(j, 12)"
SolverAdd CellRef:=Range("Cells(j, 5)"), Relation:=1,
FormulaText:="Cells(j, 13)"

SolverSolve UserFinish:=True

SolverFinish KeepFinal:=1

Next j

Help with Solver & VBA ?
 

Would any ideas here help?

Dim R As Long 'Row

For R = 14 To Cells(11, 3)
SolverReset

SolverOk Cells(R, 14).Address, 2, , Range(Cells(R, 7), Cells(R,
9)).Address
SolverAdd Cells(R, 3).Resize(1, 3).Address, 1, Cells(R, 11).Resize(1,
3).Address
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
Next R

HTH
--
Dana DeLouis
Win XP & Office 2003


"David D." wrote in message
...
I'm trying to loop through rows of data and I'm having
trouble with the cell addresses. Microsoft's Solver
discussion only utilizes specific cell addresses and I'm
trying to change the target cell and constraint cells on a
row-by-row basis. CLearly I'm not addressing the cells
correctly by I'm not sure how to do it. Any help is
greatly appreciated. Thanks!


Here's the code:

Dim i As Integer ' Number of rows
Dim j As Integer ' Start row

i = Cells(11, 3)
For j = 14 To i

SolverOk SetCell:=Range("Cells(j, 14)"), MaxMinVal:=2,
ByChange:=Range("Cells(j,7):Cells(j,9)")
SolverAdd CellRef:=Range("Cells(j, 3)"), Relation:=1,
FormulaText:="Cells(j, 11)"
SolverAdd CellRef:=Range("Cells(j, 4)"), Relation:=1,
FormulaText:="Cells(j, 12)"
SolverAdd CellRef:=Range("Cells(j, 5)"), Relation:=1,
FormulaText:="Cells(j, 13)"

SolverSolve UserFinish:=True

SolverFinish KeepFinal:=1

Next j

Help with Solver & VBA ?
 

-----Original Message-----
Would any ideas here help?

Dim R As Long 'Row

For R = 14 To Cells(11, 3)
SolverReset

SolverOk Cells(R, 14).Address, 2, , Range(Cells(R,
7), Cells(R,
9)).Address
SolverAdd Cells(R, 3).Resize(1, 3).Address, 1, Cells
(R, 11).Resize(1,
3).Address
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
Next R

HTH
--
Dana DeLouis
Win XP & Office 2003


"David D." wrote in
message
...
I'm trying to loop through rows of data and I'm having
trouble with the cell addresses. Microsoft's Solver
discussion only utilizes specific cell addresses and I'm
trying to change the target cell and constraint cells
on a
row-by-row basis. CLearly I'm not addressing the cells
correctly by I'm not sure how to do it. Any help is
greatly appreciated. Thanks!


Here's the code:

Dim i As Integer ' Number of rows
Dim j As Integer ' Start row

i = Cells(11, 3)
For j = 14 To i

SolverOk SetCell:=Range("Cells(j, 14)"), MaxMinVal:=2,
ByChange:=Range("Cells(j,7):Cells(j,9)")
SolverAdd CellRef:=Range("Cells(j, 3)"), Relation:=1,
FormulaText:="Cells(j, 11)"
SolverAdd CellRef:=Range("Cells(j, 4)"), Relation:=1,
FormulaText:="Cells(j, 12)"
SolverAdd CellRef:=Range("Cells(j, 5)"), Relation:=1,
FormulaText:="Cells(j, 13)"

SolverSolve UserFinish:=True

SolverFinish KeepFinal:=1

Next j


Thanks. I'll give it a shot. Cheers.
.