Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Thanked 0 Times in 0 Posts

    Goal Seek within VB

    Hi All,

    I am using Goal seek in VB, but instead of actually returning the result in a cell I would like to return the result in a message box so the user can enter it in the starting cell.

    Iím unable to upload workbooks so hope the baove makes sense:

    If Range("L58") - "0.000001" > Range("L25") Then

    Range("L58").GoalSeek Goal:=Range("L25").Value, _

    End If

    L58 and L25 are%

    J46 is a net column that then has various calculation added to it to then calculate the L58 %. Goal seek works on this basis.

    Thanks in advance

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Thanked 105 Times in 90 Posts
    Hopefully someone will show that I'm wrong, but I think that the way that goal seek works precludes what you are trying to do in the way you describe.

    The goal seek function writes a test value in the target cell, then recalculates the workbook, then uses the new value to compute what the next test value should be - and so on until it has iterated to the desired result - or can't get there, in which case it gives you a warning. So the process of writing into the target cell is integral to the way that goal seek functions.

    Two possibilities I can think of, offhand:

    1. My favourite: it sounds clumsy but is actually simple to code. I can think of several variations on this theme - hopefully it will stimulate your creative juices :-)
    In VBA:
    a. Disable screen updates
    a. Write a copy of the workbook to file with another name
    b. Do your goal seek in the copy and store the answer in a VBA variable
    c. Delete the copy file
    d. Enable screen updates
    e. Report the stored solution value in your message box.

    2. Most often, although not always easy, it is possible to do a direct calculation of the target cell instead of using goal seek.

    Its an unusual request - perhaps if you can describe what you are trying to achieve overall, someone might come up with another way altogether to achieve it ?
    Last edited by MartinM; 2013-04-11 at 16:33.

Posting Permissions

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