×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

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!

*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

Macro For Goal Seek?

Macro For Goal Seek?

Macro For Goal Seek?

(OP)
I frequently run "Goal Seek" under the Tools menu with little or no change to the dialog box entries.

Is there a way to use data from cell entries to eliminate re-keying the same data.

RE: Macro For Goal Seek?

RonMB,

I've done something like this in the past.  I couldn't find the actual example, but here's a macro I've just recorded; it is hopefully self-explanatory.  (I started off with a figure in cell A1, another figure in cell B1 and a formula in cell C1 thus "=A1+B1".


********************start of goalseek macro***********
Sub goal_seek()
'
' goal_seek Macro
' Macro recorded 24/08/2004 by Brian Doherty
    Range("C1").GoalSeek Goal:=273, ChangingCell:=Range("B1")
End Sub
********************end of goalseek macro****************

Hope this helps,

Brian

RE: Macro For Goal Seek?

You should learn to use the macro recorder.  This is what I get:
    Range("B1").GoalSeek Goal:=15, ChangingCell:=Range("A1")


TTFN

RE: Macro For Goal Seek?

(OP)
I did try using the recorder but I was hoping to be able to obtain the goal from a cell also.  Is that part not possible?

RE: Macro For Goal Seek?

    Range("B1").GoalSeek Goal:=Range("D1").Formula, ChangingCell:=Range("A1")


TTFN

RE: Macro For Goal Seek?

(OP)
It does it fine! Thanks a bunch!

RE: Macro For Goal Seek?

If I understand your post, here is one way:

Let cell A1 = the target result(type it in)
Let cell B1 = the changing input value
Let cell C1 = the computed result(linked to the calculations)

Let cell D1 = A1 - C1

Now record a macro to Goalseek Cell D1 to value 0 (zero) by changing cell B1.  Stop recording.

To use the macro, type your target into cell A1 and run the macro.  Cell B1 will be the solution (input that gives you the desired result).

If you are going to use it a lot, you can insert a textbox or other object and assign the macro to it, so you have a "push button" to run it.

RE: Macro For Goal Seek?

(OP)
Okay, now that that part works, what is the syntax to bring in the addresses of the calcualted result and the changing cell.  I tried the following but got an error:

Range("B1").value.GoalSeek Goal:=Range("D1").Formula, ChangingCell:=Range("A1").value2

RE: Macro For Goal Seek?

I think you are over-complicating it.  This macro works with my approach from above:

Sub test()
'
' test Macro
' Macro to demonstrate goal seeking using cell values
' instead of dialogue box

'
    Range("D1").Select
    Range("D1").GoalSeek Goal:=0, ChangingCell:=Range("B1")
End Sub

Note that because the formula in cell D1 (on the worksheet) already references both the target (cell A1) and the computed values (cell C1, or whatever other cell has the computed value) from cells on the worksheet, those same cells are not referenced in the macro.

RE: Macro For Goal Seek?

    Range(Range("E1").Text).GoalSeek Goal:=Range("C1").Value, ChangingCell:=Range(Range("D1").Text)


TTFN

RE: Macro For Goal Seek?

(OP)
Thanks for all your help!

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! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close