Macro For Goal Seek?

[RonMB]

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.

 

[briand2]

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

 

[IRstuff]

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



TTFN

 

[RonMB]

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?

 

[IRstuff]

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



TTFN

 

[RonMB]

It does it fine! Thanks a bunch!

 

[bltseattle]

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.

 

[RonMB]

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

 

[bltseattle]

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.

 

[IRstuff]

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



TTFN

 

[RonMB]

Thanks for all your help!