Goal Seek with variable Set Cell

Discussion in 'Excel Beginners' started by raith99, Aug 8, 2013.

  1. raith99

    raith99 Guest


    I'm relatively new to excel and would appreciate any help with th
    following problem.

    I'm working on a pensions spreadsheet and need to work out income a
    retirement over a set period. I have tables which have all the require
    formulas and a manual goal seek works fine if I enter the set cell o
    the corresponding period entered by the customer. Unfortunately th
    period can change, i.e. some may want the income for 15 years, others 2
    years or whatever.

    I realise to automate this I need a macro but I don't know how to d
    this with a variable set cell. I've tried with vlookup but that doesn'
    work. I've worked out a formula to give me the location of the set cel
    but again have no idea how to get this to work with a goal seek macro.

    Column A has the number of years (1-30)
    Column B has the annual income
    Column C has the total fund value

    So if required period 15 years the Set Cell would be C15, if 20 years
    C20, etc.
    Goal Value would be 0
    Changing Cell would be B1

    Hope you can help before I pull all my hair out
    raith99, Aug 8, 2013
    1. Advertisements

  2. raith99

    joeu2004 Guest

    You might not need to use Goal Seek at all. It depends on what your
    variables are.

    I suggest that you post the URL of an example Excel file that you uploaded
    to a file-sharing website. The following is a list of some free
    file-sharing websites; or use your own.

    Box.Net: http://www.box.net/files
    Windows Live Skydrive: http://skydrive.live.com
    MediaFire: http://www.mediafire.com
    FileFactory: http://www.filefactory.com
    FileSavr: http://www.filesavr.com
    RapidShare: http://www.rapidshare.com

    Assuming you are familiar with writing VBA macros, try the following code
    fragment. I assume that data starts in row 1, and the number of years is 2
    or more.

    Dim lastRow As Long
    lastRow = Range("A1").End(xlDown).Row
    Range("C" & lastRow).GoalSeek Goal:=0, ChangingCell:=Range("B1")

    ----- original message -----
    joeu2004, Aug 8, 2013
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.