×
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

Automatic goal seek
3

Automatic goal seek

Automatic goal seek

(OP)
I have prepared a spreadsheet to calculate psychrometric properties. This requires solving two implicit equations when the inputs are temperature and RH. Presently, I am using goal seek function. Is there a way to activate the goal seek function without manually doing it every time, when the inputs are changed?

I am using goal seek for the following case.

Cell C7 is assume value and cell C8 is calculated value. I am using goal seek in cell F7 (which is C8 minus C7) to Set cell:F7 To value:0 By changing cell:C7

RE: Automatic goal seek

Use a command button and assign a macro to it. Best way is to record the steps you use into a macro.

corus

RE: Automatic goal seek

2
The code below will run goal seek any time the worksheet is changed by the user.  It should not add much to sheet processing time if the changed cells are not input cells that affect this calculation, because it should already be at the goal.  If, for some reason, you only want to Goal Seek when certain cells are changed, you will have to test the value of Target to see if any cells of interest are contained in it.

CODE

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bSuccess As Boolean

    On Error Resume Next
    bSuccess = Range("F7").GoalSeek(0, Range("C7"))
    On Error GoTo 0
    If Not bSuccess Then
        MsgBox "Goal Seek Failed for Cell ""C7""!"
    End If

End Sub

-handleman, CSWP (The new, easy test)

RE: Automatic goal seek

Another approach is to use circular cell references and switch iteration on, if you want to avoid vba. However handleman's approach is much easier to understand and doesn't get into horrible loops when errors occur.

 

Cheers

Greg Locock

SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Automatic goal seek

(OP)
Excellent handleman! That worked perfectly. The cells in goal seek are not input cells.

Corus,

Thanks, I did it yesterday after your suggestion, an easy and nice solution. The only problem was that the spreadsheet has two types of inputs with user preference. Providing a command button only to one set of calculations and running the other set automatically didn't look good. Nevertheless, you have an excellent idea for me to use in future.

Greg,

Tried the iteration method but was getting wrong values. Each parameter calculation has more than two if loops (depending upon the valid temperature range) and are implicit. Iteration might have worked but I didn't have enough patience to go through the formulae one by one and removing the circular reference.

Thanks to you all, once again.

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