Excel Solver: Set target cell to a cell reference instead of a value 1

[OilBoiler]

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!

 

[yakpol]

Dilboiler,
You can record a macro using solver and than to modify it replacing target value my range("A1").value

 

[OilBoiler]

Thanks yakpol,

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?

 

[yakpol]

Instead of "$F5" write

Range("F5").Value

also you can modify the last line to eliminate the solver messages:

SolverSolve Userfinish:=True

Should work!

 

[OilBoiler]

I tried it again, but now it's giving me the error on "SolverOK" and it says that the Sub or function is not defined

Sub Macro2()
'
' 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

 

[bltseattle]

Set up an equation in a cell, say:
A1=Targetvalue-myvalue
Using the cell reference as the target value
goalseek A1 till it equals 0.

 

[francesca]

I tried to record a macro using solver earlier this week (using Excel 2003) and it did not work. I was irate at having to use solver about fifty times, and couldn't find solver in the Object Browser.

 

[electricpete]

I don't know what an object browser is.

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.

 

[francesca]

The object browser is within the VBEditor screen. It allows you to browse through all the VBA objects, functions, properties and methods.

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

 

[GregLocock]

Typically when I'm trying to optimise for several outcomes I build a 'score' which is a weighted average, typically the square of the deviation of each parameter from the target.

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.

 

[yakpol]

Oilboiler and francesca,
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!

 

[Gryantaylor]

Another option would be to subtract the value you want from the target cell and then set solver to a value of zero (or min if you use the abs function).

With this method solver has the same settings everytime and you don't need to learn VB.