Goal Seek with variable Set Cell

[raith99]

Hi,

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

 

[joeu2004]

I'm working on a pensions spreadsheet and need to work
out income at retirement over a set period. I have tables
which have all the required formulas and a manual goal
seek works fine

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

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

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 -----