Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    I am having trouble with some code which is supposed to cause Goal Seek to run any time there is a calculation on the sheet.

    I found this code on the web, modified it to use named cells, and inserted in a worksheet module (not a normal module):

    Private Sub Worksheet_Change(ByVal Target As Range)

    Range("Goal").GoalSeek Goal:=0, ChangingCell:=Range("changing_cell")

    End Sub

    In this code, “Goal” is the “Set Cell” in goal seek
    It is supposed to be set to a value of 0.
    “Changing_cell” is the name of the cell to be changed to make the “Goal” = 0.

    I found that it worked on a very simple model; but now in an only slightly more complex one, it doesn’t seem to work at all.(calculation is set to automatic, by the way)

    Attached are the working and buggy examples. Both are very simple and I have taken pains to make the problem clear with formatting and notes. The user-changeable variables are in blue font; any time one of these changes, the Goal Seek macro is supposed to change. Can the code in the second model be fixed so that the Goal Seek macro runs anytime there’s a change on the worksheet?

    I’d be very grateful for any help. If this could be made to work it would be very useful.

    By the way, I am pretty weak in Excel VBA, so please bear that in mind in any reply !

    Regards

    lingyai

    (PS You don’t need to worry about what the models actually calculate -- though if you are interested, it is to calculate the size of identical annual payments to be made for so many years, given the number of years, the interest rate and the amount of money needed at the end of those years; but for specific reasons I want to use Goalseeek, not a formula or one of Excel’s native functions. )
    Attached Files Attached Files

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Your goal seek cell (E19) does not have a formula in it. So changing other cells will not have any effect on that cell.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The code is not running when calcuations are being made, it runs when a cell or cells triggers a change.

    The spinners you are using will NOT trigger a change event so do not trigger the code to run.

    You can get the spinners to recalc goal seek by creating the goal seek code in module and have the change event call the code and also assign the macro to the spinner event.

    Alternately you could assign the code to the calculation event.

    Also I would add the line Whether in a change or calc event):
    before running the code and then after the code add the line:
    Application.EnableEvents = true

    to prevent recursive calls to the routine when the code changes the cells...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •