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.
Is there a way to use data from cell entries to eliminate re-keying the same data.





RE: Macro For Goal Seek?
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?
Range("B1").GoalSeek Goal:=15, ChangingCell:=Range("A1")
TTFN
RE: Macro For Goal Seek?
RE: Macro For Goal Seek?
TTFN
RE: Macro For Goal Seek?
RE: Macro For Goal Seek?
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?
Range("B1").value.GoalSeek Goal:=Range("D1").Formula, ChangingCell:=Range("A1").value2
RE: Macro For Goal Seek?
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?
TTFN
RE: Macro For Goal Seek?