Excel/Goal Seek

Advertisement


Question
QUESTION: I want to automate the Goal Seek command in an Excel worksheet (Windows 7, .xlsx file), and havenít been able to accomplish the task. (Do I need to save the file first as an .xlsm file?)

The cell locations of my Goal Seek targets are:

Set cell:  H29 (contains a formula)
To value:  C11 (manually entered)
By changing cell:  C33 (contains a formula)

My desire is for Goal Seek to automatically run each time the value in cell C11 is changed.

I donít know anything about VBA, nor have I ever written a macro.

How can I do this?

Thank you

ANSWER: Hi Ted,

Yes, the file will need to be saved as a .xlsm file because it will contain VBA code.

What you describe is easy to do and I will provide instructions as to how to do it.  But there is a problem with your problem description.  You state that Goal Seek should set H29 to value in C11 by changing C33.  But Goal Seek won't be able to change the value in C33 without wiping out the formula that you say is there.  

1. Is there actually a formula in C33?

2. If there is then Goal Seek will have to change a cell that C33 depends on rather than C33 directly.  If this is the case, then what is that cell?  

You can answer this using a followup question.

Damon

---------- FOLLOW-UP ----------

QUESTION: Hi Damon,

I checked, and there is NOT a formua in cell C33. I only thought there was, since the cell is apparently changed by a routine in my spreadsheet for which I was not the author.

Therefore, the corrected cell information is as follows:

Set cell:  H29 (contains a formula)
To value:  C11 (manually entered)
By changing cell:  C33 (manually entered)

Thank you,

Ted

Answer
Hi Ted,

Thank you for answering my questions. Here is how to do it:

Enter the following code into the worksheet's code module.  To to this, right-click on the worksheet's tab, select View Code option, then copy and paste this code into the Code pane:

_________________________________________

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$C$11" Then
     Range("H29").GoalSeek Goal:=Range("C11"), ChangingCell:=Range("C33")
  End If
End Sub
_________________________________________

This code will monitor cell C11 for a change, and when it does it will run the GoalSeek method to change the value of C33 to get H29 to the value of C11.  Depending on the mathematics of the formula in H29 you many have to provide an initial guess at the correct value in C33 that comes more or less close to the solution value.

And again you will have to save as .xlsm and enable macros when you re-open the file.

Damon
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.