Results 1 to 6 of 6

Goal Seek Macro

This is a discussion on Goal Seek Macro within the Excel Questions forums, part of the Question Forums category; I am trying to create a macro that allows a cell reference to be used for the "To Value" field ...

  1. #1
    New Member
    Join Date
    Jul 2002
    Posts
    2

    Default

    I am trying to create a macro that allows a cell reference to be used for the "To Value" field in Goal Seek. I have created the code, but it will only work if I put a number value in for the "To Value." If this can't be done, is there a macro I can create to set an equation equal to a referenced cell by changing another parameter? I'd like to make it so that the spreadsheet user for my particular program does not have to go through the Goal Seek process every time the data is changed. If anyone could please help I would appreciate it. Thank you.

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default

    Hi,

    Here is one example, placed in the change event of the worksheet module:
    ---------------------------------------------
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Row = 2 And Target.Column = 2 Then
    Range("C5").GoalSeek Goal:=Range("B5").Value, _
    ChangingCell:=Range("C2")
    End If
    End Sub
    ----------------------------------------
    Any time cell B2 is changed (target row and column = 2), the goal seek procedure is triggered, setting cell C5 to equal cell B5 by changing cell C2.

    HTH,
    Jay

  3. #3
    New Member
    Join Date
    Jul 2002
    Posts
    2

    Default

    Hi,
    I tried using this:

    Here is one example, placed in the change event of the worksheet module:
    ---------------------------------------------
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Row = 2 And Target.Column = 2 Then
    Range("C5").GoalSeek Goal:=Range("B5").Value, _
    ChangingCell:=Range("C2")
    End If
    End Sub
    ----------------------------------------
    Any time cell B2 is changed (target row and column = 2), the goal seek procedure is triggered, setting cell C5 to equal cell B5 by changing cell C2.
    .............
    When I change a parameter that causes the value in B5 to change, the resulting value in C5 does not perform the Goal Seek operation correctly to match this value. Am I doing something wrong? The cell B2 serves only to trigger the procedure correct?

    Thanks.

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default

    Hi,

    Correct. You have to change the references to suit your data. If you are still having trouble after changing the references, please post some details so that we can see the problem.

    Bye,
    Jay

  5. #5
    New Member
    Join Date
    Apr 2009
    Posts
    1

    Default Re: Goal Seek Macro

    Jay, can you explain how to use this goal seek function if a number changes on a worksheet outside of the one we are modifying? I.e., how do you reference a range of cells as target cells from a different sheet?

  6. #6
    New Member
    Join Date
    Dec 2009
    Posts
    2

    Default Re: Goal Seek Macro

    Very good tip! Worked perfectly for me. Is there someway to change it so it actualizes automatically when a cell in another sheet changes?

    Thx

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com