+ Reply to Thread
Results 1 to 12 of 12

Automatic Goal Seek

  1. #1
    Registered User
    Join Date
    10-23-2012
    Location
    Kuwait
    MS-Off Ver
    Excel 2010
    Posts
    7

    Automatic Goal Seek

    Hello everyone,

    I was wondering if anyone could help me with my Goal Seek problem. I have to manually set goal seek by opening the menu and selecting the cells every time I want to set a down payment or backward calculate a profit rate from a specific installment value. This gets rather hectic when I keep making changes. Is there away where I can use a macros to just set and empty cell for the down payment and one for the installment (monthly rental on the excel sheet attached), so I can enter a number for each and Goal Seek will be calculated automatically?

    Kindly find attached is a copy of the sheet I am referring to with notes on how I use goal seek and which cells I would like to set to calculate goal seek automatically.

    Thanks a lot and I greatly appreciate you time and efforts.

    Ciao,
    -T-

    P.S. I need the macros to do this for various cars. Thanks again.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Automatic Goal Seek

    Surprisingly easy to do, I must say. I should use it more often.

    There is also a possibility to trigger it on changing values in the sheet.

    Here is an example of what the code looks like
    Please Login or Register  to view this content.
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Registered User
    Join Date
    10-23-2012
    Location
    Kuwait
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Automatic Goal Seek

    Jacc,

    Thanks a lot that's amazing. Much appreciated.

    -T-

  4. #4
    Registered User
    Join Date
    10-23-2012
    Location
    Kuwait
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Automatic Goal Seek

    Jacc,

    Can you help me with one last thing, What VBA code would I need to add to the code to run the macro based on inputs from various sheets?

    i.e.

    Please Login or Register  to view this content.

    Thanks a lot again
    Last edited by arlu1201; 05-09-2013 at 01:56 AM.

  5. #5
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Automatic Goal Seek

    Glad to hear it works!

    That would look like this:
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-23-2012
    Location
    Kuwait
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Automatic Goal Seek

    Thanks again for your quick reply. You have been very help and I feel bad to keep bugging you like this. However I seem to be having difficulty getting it to run I keep getting "Run-time error '9': Subscript out of range" with the coding below:

    Sub GoalSeek()

    Sheets("Sheet3").Range("H35").GoalSeek Goal:=Sheets("Sheet2").Range("E16").Value, ChangingCell:=Sheets("Sheet3").Range("G35")

    End Sub

  7. #7
    Registered User
    Join Date
    10-23-2012
    Location
    Kuwait
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Automatic Goal Seek

    No worries I figured it out. Thanks alot you have been a great help. God bless

  8. #8
    Registered User
    Join Date
    05-08-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Automatic Goal Seek

    Hi Jacc,
    Could you please explain how you worked it out in a simplified method step by step. I don't have a good understanding in VBA & really want to use this method to do some work.
    Thanx in advance

    Regards
    Josh

  9. #9
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Automatic Goal Seek

    I'd be happy to do that, I like explaining. However, since we are on text basis here you have to be more specific. I hate to write a minor essay about how to navigate the VBA editor just to find out you already knew that, if you see what I mean. Also, the internet is loaded with Excel VBA tutorials. That being said, if you can be more specific about what you want explained I'll try and help you.

    Is it getting started in VBA that is your problem? You don't know what to do after you hit Alt + F11?
    http://www.functionx.com/vbaexcel/Lesson02.htm

  10. #10
    Registered User
    Join Date
    05-08-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    2

    Talking Re: Automatic Goal Seek

    Quote Originally Posted by Jacc View Post
    I'd be happy to do that, I like explaining. However, since we are on text basis here you have to be more specific. I hate to write a minor essay about how to navigate the VBA editor just to find out you already knew that, if you see what I mean. Also, the internet is loaded with Excel VBA tutorials. That being said, if you can be more specific about what you want explained I'll try and help you.

    Is it getting started in VBA that is your problem? You don't know what to do after you hit Alt + F11?
    http://www.functionx.com/vbaexcel/Lesson02.htm
    Hi Jacc,

    Thanx for the quick response mate. I'll check the link & let you Know how it'll go.
    Thanx again man

    Cheers,
    Josh

  11. #11
    Registered User
    Join Date
    01-03-2007
    Posts
    7

    Re: Automatic Goal Seek

    I had the same issue and I got the goalseek to work, but how do you automate it. I want it to update whenever the set to value # changes?

    *Edit: Ok I got it to work on Workshee_Calculate so it is automated now. But the problem is now that it gives me an error: Run-time error'-2147417848 (800010108)':
    Method 'GoalSeek" of object 'Range' failed.

    Not sure what this means.
    Last edited by twychopen; 10-04-2013 at 10:48 AM. Reason: Runtime error

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Automatic Goal Seek

    twychopen,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1