Goal Seek Macro

[keanek]

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.

 

[Jay Petrulis]

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

 

[keanek]

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.

 

[Jay Petrulis]

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

 

[WarrenGoldenGate]

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?

 

[fdelriog]

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