[Webinar] Streamline your web hosting managementRegister Today


Excel 5.0 Macro

Posted on 2000-01-02
Medium Priority
Last Modified: 2012-08-13
I am trying to create a simple macro that runs a Goal Seek, but also displays an input box to allow the user to enter the proposed value.

Sub Goal_Seek()
Worksheets("Negotiation Scenario").Activate

myNum = Application.InputBox("Enter a number")
    ActiveCell.GoalSeek Goal:=myNum, ChangingCell:=ActiveCell.Offset(-2, 0). _
End Sub

The only result I get is that the reference is not valid.
I know I am probably way off mark but can anyone help?
Question by:kenny1
LVL 22

Accepted Solution

ture earned 280 total points
ID: 2319109

Your code should work, provided that:

1. The active cell is the cell with a formula that you want to be equal to mynum.

2. The cell to change is the cell two steps above the active cell.

Perhaps you should specifically name the ranges where you want to do your goal seeking. Something like this:

Sub Goal_Seek()
  Worksheets("Negotiation Scenario").Activate
  myNum = Application.InputBox("Enter a number")
  Range("D9").GoalSeek Goal:=myNum, ChangingCell:=Range("D7")
End Sub

Ture Magnusson
Karlstad, Sweden

Author Comment

ID: 2319165
Thankyou very much.
I have been banging my head over this problem and getting no where.
I appreciate the help.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In this post, we will learn to set up the Group Naming policy and will see how it is going to impact the Display Name and the Email addresses of the Group.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question