VBA Excel Solver

[smitpau]

Hi,

I would like to combine two actions into a Macro, one as a Solver, the other to link Solver object cell (Sheet9, G39) to a cell in another worksheet (Sheet1, E82) as sometimes it's just a manual input.

The Solver code and the linking cell code work independently fine but for some reason when combined the solver can't find an answer.

Wonder if it's the way the cells are referenced.

Thanks for reading!

Any suggestions appreciated.

Cell Reference Code
Worksheets("Sheet1").Activate
Range("E82").Select
ActiveCell.Formula = "=Sheet9!G39"

Solver Code
SolverReset
SolverOk SetCell:="$A$39", MaxMinVal:=1, ValueOf:=0, ByChange:="$G$39", Engine _
:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$A$39", Relation:=2, FormulaText:="$E$39"
SolverAdd CellRef:="$G$39", Relation:=1, FormulaText:="$A$40"
SolverOk SetCell:="$A$39", MaxMinVal:=1, ValueOf:=0, ByChange:="$G$39", Engine _
:=1, EngineDesc:="GRG Nonlinear"""
SolverOptions MaxTime:=0, Iterations:=0, Precision:=0.000001, Convergence:=0.49 _
, StepThru:=False, Scaling:=False, AssumeNonNeg:=True, Derivatives:=1
SolverOptions PopulationSize:=150, RandomSeed:=0, MutationRate:=0.075, Multistart _
:=False, RequireBounds:=True, MaxSubproblems:=0, MaxIntegerSols:=0, _
IntTolerance:=1, SolveWithout:=False, MaxTimeNoImp:=30
SolverOk SetCell:="$A$39", MaxMinVal:=1, ValueOf:=0, ByChange:="$G$39", Engine _
:=1, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$A$39", MaxMinVal:=1, ValueOf:=0, ByChange:="$G$39", Engine _
:=1, EngineDesc:="GRG Nonlinear"
SolverSolve

 

[smitpau]

No problem this has been solved now.

Had to select the original sheet where the solver is being used again

Cell Reference Code (Updated)
Worksheets("Sheet1").Activate
Range("E82").Select
ActiveCell.Formula = "=Sheet9!G39"
Sheets("Sheet1").Select
Range("H39").Select