Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in
  • Congratulations KootK on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
OilBoiler

OilBoiler

Chemical
Aug 5, 2003
43
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!
 
Replies continue below

Recommended for you

Sort by date Sort by votes
Dilboiler,
You can record a macro using solver and than to modify it replacing target value my range("A1").value
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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).
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dik
  • Locked
  • Question
Convert Cell Types 1
Replies
10
Views
307
dik
dik
BhagyeshVashi
  • Locked
  • Question
Need a rename macro for excel
Replies
11
Views
525
sdz
sdz
Kenja824_home
  • Locked
  • Question
Excel - Looking for a formula please 2
Replies
14
Views
335
BridgeSmith
BridgeSmith
electricpete
  • Locked
  • Question
an update to vba routine for highlighting the current cell 3
Replies
6
Views
119
skeletron
skeletron
Kenja824
  • Locked
  • Question
Creating a schedule?
Replies
17
Views
368
Kenja824
Kenja824

Part and Inventory Search

Sponsor

Back
Top Bottom