Solver VBA

[kwrohde]

Currently I have a simple solver VBA script in my excel workbook. The script sets the value of cell "I1" to 1 by changing cell "H1", as shown below:

Sub repeatsolve()
'
SolverOk SetCell:="$I$1", MaxMinVal:=3, ValueOf:="1", ByChange:="$H$1"
SolverSolve
End Sub

What I would like to do is set the value of all cells in the "I" column, of a specific range, to 1 by changing its respective cell in column "H". Does anyone have any suggestions? Thank in advance.

[Tushar Mehta]

Try the untested

Sub repeatSolver()
Dim aRng As Range, aCell As Range
With ActiveSheet
For Each aCell In .Range(.Range("I1"), .Range("I1").End(xlDown))
solverreset
SolverOk SetCell:=aCell.Address, MaxMinVal:=3, ValueOf:="1", _
ByChange:=aCell.Offset(0, -1).Address
SolverSolve
Next aCell
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
>
> Currently I have a simple solver VBA script in my excel workbook. The
> script sets the value of cell "I1" to 1 by changing cell "H1", as shown
> below:
>
> Sub repeatsolve()
> '
> SolverOk SetCell:="$I$1", MaxMinVal:=3, ValueOf:="1",
> ByChange:="$H$1"
> SolverSolve
> End Sub
>
> What I would like to do is set the value of all cells in the "I"
> column, of a specific range, to 1 by changing its respective cell in
> column "H". Does anyone have any suggestions? Thank in advance.
>
>
> --
> kwrohde
> ------------------------------------------------------------------------
> kwrohde's Profile: http://www.excelforum.com/member.php...fo&userid=5721
> View this thread: http://www.excelforum.com/showthread...hreadid=501834
>
>