Excel Solver: Set target cell to a cell reference instead of a value
Excel Solver: Set target cell to a cell reference instead of a value
(OP)
Hi everyone,
I'm working on reconciling a mass balance and I'm trying to set one of my flows to zero. I'm using the solver in Excel to do this, but I don't want to set my target cell to a max, min, or a typed-in value. I'm taking online data from our DCS system, so I want to be able to set my target cell to that value that the DCS system pulls up at that specific time. In other words, my target cell can't be set to a specific value that has to be typed in. I want instead of a value, a cell reference, because the target I want will be changing constantly. It's not a constant I can just type in. Do you know how I can do that in the Solver or differently?
Thanks!
I'm working on reconciling a mass balance and I'm trying to set one of my flows to zero. I'm using the solver in Excel to do this, but I don't want to set my target cell to a max, min, or a typed-in value. I'm taking online data from our DCS system, so I want to be able to set my target cell to that value that the DCS system pulls up at that specific time. In other words, my target cell can't be set to a specific value that has to be typed in. I want instead of a value, a cell reference, because the target I want will be changing constantly. It's not a constant I can just type in. Do you know how I can do that in the Solver or differently?
Thanks!





RE: Excel Solver: Set target cell to a cell reference instead of a value
You can record a macro using solver and than to modify it replacing target value my range("A1").value
RE: Excel Solver: Set target cell to a cell reference instead of a value
I'm not really familiar with macros, so I recorded it and tried to edit it and this is what I had:
Sub MassBal()
'
' MassBal Macro
' Macro recorded 12/09/2005 by cabelkm
'
' Keyboard Shortcut: Ctrl+Shift+M
'
SolverOk SetCell:="$Q$5", MaxMinVal:=3, ValueOf:="143127", ByChange:="$AA$5"
SolverSolve
End Sub
I want to change the 143127 to a cell reference. How do I write that? Like this?
SolverOk SetCell:="$Q$5", MaxMinVal:=3, ValueOf:="$F5", ByChange:="$AA$5"
SolverSolve
Is that how I write it?
RE: Excel Solver: Set target cell to a cell reference instead of a value
CODE
CODE
RE: Excel Solver: Set target cell to a cell reference instead of a value
CODE
'
' Macro2 Macro
' Macro recorded 12/09/2005 by cabelkm
'
'
SolverOk SetCell:="$Q$5", MaxMinVal:=3, ValueOf:=Range("F5").Value, ByChange:="$AA$5"
SolverSolve
End Sub
RE: Excel Solver: Set target cell to a cell reference instead of a value
A1=Targetvalue-myvalue
Using the cell reference as the target value
goalseek A1 till it equals 0.
RE: Excel Solver: Set target cell to a cell reference instead of a value
RE: Excel Solver: Set target cell to a cell reference instead of a value
But I do know that if you record a macro with the functions you need like OilBoiler did, then you can look at the code to see what vba functions were invoked. If I have understood your problem, that should help you find objects. If I have misunderstood your problem, I apologize.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Excel Solver: Set target cell to a cell reference instead of a value
I suspect that I couldn't find the SolverOK and SolverSolve functions because I don't have an add-in installed (for instance, I don't have Goal Seek installed).
RE: Excel Solver: Set target cell to a cell reference instead of a value
I then use Solver to minimise this score.
Cheers
Greg Locock
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Excel Solver: Set target cell to a cell reference instead of a value
It takes some more VBA settings to work with solver using macros. The SOLVER automation library has to be referenced. To do so in VBA editor click Tools-References and check SOLVER library. Don't forget to save the file!
RE: Excel Solver: Set target cell to a cell reference instead of a value
With this method solver has the same settings everytime and you don't need to learn VB.