CODEWITHSUNDEEP
excelvbasolver
Fitzy
Fitzy

Reputation: 113

Solver VBA ActiveCell "stuck"

I am attempting to use a macro to invoke Solver. The goal is to select a Target cell (ActiveCell) which will be the "ByChange: " cell. The SetCell is the cell next column over, which should = 0. When I want to change the activecell by selecting the next cell to run the macro on, it should evaluate it. However it appears that the original activecell target is being held in memory after the first run.

Is there a way of clearing the memory for activecell so that the newly selected Target cell registers as the activecell?

Sub Solver()
    Dim Target As Double
    Dim zero As Double
    Target = ActiveCell.Value
    zero = Cells(ActiveCell.Row, ActiveCell.Column + 1).Value

    SolverOk SetCell:=zero, MaxMinVal:=3, ValueOf:=0, ByChange:=Target, Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve UserFinish:=True
End Sub

Upvotes: 0

Views: 439

Answers (1)

YowE3K
YowE3K

Reputation: 23974

I believe you are trying to do

Sub Solver()
    SolverOk SetCell:=ActiveCell.Offset(0, 1), _
             MaxMinVal:=3, _
             ValueOf:=0, _
             ByChange:=ActiveCell, _
             Engine:=1, _
             EngineDesc:="GRG Nonlinear"
    SolverSolve UserFinish:=True
End Sub

i.e. get the cell one column to the right of the ActiveCell to have a value of zero, by changing the value in the ActiveCell.

Upvotes: 1

Related Questions