We help IT Professionals succeed at work.

Reproducing the Goal-Seek function to eliminate circular references

2,312 Views
Last Modified: 2012-05-05
Please forgive my ignorance on the subject, but my client has just asked me about the "Goal Seek" tool in Excel.  They would like to know if the method it uses to perform its calculations can be reproduced using VBA code.  My client has made some attempts to write calculations that are like the Goal Seek function, which has resulted in circular references, which we are clear need to cleaned up.
When you respond, please include a brief description of the function and when it's useful; useful articals that describe the function with concrete examples, and any sample VBA code that performs the iterative calculations internally.  
I suspect I will become an expert on the subject before too long.  --Peter Ferber
Comment
Watch Question

Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
GoalSeek is useful if you have a single goal and need to change only one cell to reach it.

Solver is required if you need to change more than one cell to reach your goal. It may also be used when only one cell needs to be changed. Solver exposes many more choices in the method used to reach the solution than does GoalSeek.

Here is a simple macro that uses GoalSeek to find a solution:

Sub GoalSeeker()
Dim ChangeCell As Range, Target As Range
Dim Goal As Double
Set ChangeCell = [B8]
Set Target = [D8]
Goal = 5
Target.GoalSeek Goal:=Goal, ChangingCell:=ChangeCell
End Sub


Brad

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.