Button to do goal seek

[Gene Augustin]

MAC Powerbook G4, OS 10.5.8
Office 2004, Excel 2004 Version 11.5.3


I'm trying to figure out how to add a button to my spreadsheet to use
specified cells (either named or by, for example, "B3" type callout) to do a
goal seek. When I click the button, it should bring up a dialog box to input
the "to Value" of the goal seek command. Click OK should then execute the
Goal seek and assume a value has been found and accept the value.

I can't even figure out how to just add a button. I could probably do the
rest if I had the code for a button.

 

[CyberTaz]

Hi Gene;

I must not have a complete understanding of exactly what you want to
accomplish... From your description it sounds like you want to create a
rather complex method to accomplish something which requires no more than
selecting Tools> Goal Seek. If you select the cell containing the formula
before doing so the Set Cell will already be specified so you need complete
only the other 2 fields.

If you are actually looking for something other than that please provide a
more exact & detailed description of whatever it is.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Gene Augustin]

Hi Bob,
This is just a small part of a very long macro for a design project.

I really don't need to use a button. I already know the input cells and
values for the GOAL SEK, so I am trying to do a goal seek from within a
macro. In the following snippit, everything works except the last line:

Range("QReal").GoalSeekGoal:=Range("Qguess"), ChangingCell:=Range("Phi")


Generates "Runtime error 1004, Reference not valid"

The problem seems to be with how to call out the GoalSeek Goal.

Here's the whole snippit:

Sub BeltCenterDist()
'
Range("QCalc").Select
Selection.Copy
Range("Qguess").Select
Selection.PasteSpecial Paste:=xlValues
Range("QGuess").Select 'get value, not equation
Selection.Copy
Range("QReal").GoalSeek Goal:=Range("Qguess"), ChangingCell:=Range("Phi")
'Goal seek Doesn't work

End Sub

 

[CyberTaz]

Ah... Unfortunately I'm not a VBA Guy, so I'm afraid you'll have to wait for
someone else to come along. In the meantime, though, I did notice that ‹ if
your info is stated correctly ‹ you're seriously behind on your Office
updates. You indicated 11.5.3, but 11.5.6 is current [and 11.5.5 was a
'biggie']. I doubt it has any bearing on the issue, but should be tended to.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Gene Augustin]

Persistence paid off. I finally got it. It required two lines of code for
the goal seek"

Application.CutCopyMode = False
Range("QReal").GoalSeek Goal:=Range("QGuess"), ChangingCell:=Range("Phi")

I kept getting errors until I added the first line above. I found this by
recording a macro to only goal seek and found that the extra line was in the
generated code.

Ah... Unfortunately I'm not a VBA Guy, so I'm afraid you'll have to wait for
someone else to come along. In the meantime, though, I did notice that ‹ if
your info is stated correctly ‹ you're seriously behind on your Office
updates. You indicated 11.5.3, but 11.5.6 is current [and 11.5.5 was a
'biggie']. I doubt it has any bearing on the issue, but should be tended to.

Regards |:>)
Bob Jones
[MVP] Office:Mac

Hi Bob,
This is just a small part of a very long macro for a design project.

I really don't need to use a button. I already know the input cells and
values for the GOAL SEK, so I am trying to do a goal seek from within a
macro. In the following snippit, everything works except the last line:

Range("QReal").GoalSeekGoal:=Range("Qguess"), ChangingCell:=Range("Phi")


Generates "Runtime error 1004, Reference not valid"

The problem seems to be with how to call out the GoalSeek Goal.

Here's the whole snippit:

Sub BeltCenterDist()
'
Range("QCalc").Select
Selection.Copy
Range("Qguess").Select
Selection.PasteSpecial Paste:=xlValues
Range("QGuess").Select 'get value, not equation
Selection.Copy
Range("QReal").GoalSeek Goal:=Range("Qguess"), ChangingCell:=Range("Phi")
'Goal seek Doesn't work

End Sub