×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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!

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

Dilboiler,
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

(OP)
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?

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

Instead of "$F5" write

CODE

Range("F5").Value
also you can  modify the last line to eliminate the solver messages:

CODE

SolverSolve Userfinish:=True
Should work!

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

(OP)
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 :(

CODE

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

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

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.

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

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.  

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

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.

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

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

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

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.

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

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!

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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources