Help with Solver & VBA ?

[David D.]

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

 

[Dana DeLouis]

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

 

[David D.]

-----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




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