PDA

View Full Version : Solved: Goalseek error



Glaswegian
09-17-2009, 02:18 AM
Hi

I'm trying to run a simple goalseek but I keep receiving a "1004 - application error" - and I cannot work out why.

The workbook is a financial calculator. I need to get to zero to determine a final payment amount, which will usually be different from the regular payment amount.

Sub GoalSeeker()
Calculate
Sheets("Lists").Range("J3").GoalSeek goal:=0, changingcell:=Sheets("Start").Range("E22")
End Sub

Users input some basic data and behind the scenes I work out fractions of a year, interest, take account of holidays etc and then create a basic payment schedule for users.

I've only used Goalseek once before and it worked fine so I'm a bit unsure why I'm receiving the error.

Thanks.

mdmackillop
09-17-2009, 04:20 AM
It works for me apart from rounding error (7.99 E-15) Is there a solution if to try it "manually"?

Glaswegian
09-17-2009, 04:31 AM
Thanks Malcolm.

I'm using two functions to calculate Business Days and I've been checking them to see if there are any issues, but they both work fine. I suppose I was wondering if they were impacting on Goalseek but it appears not.

I'll need to check everything I've done and see if there is anything else that might be causing the problem.

mdmackillop
09-17-2009, 04:41 AM
Can you assemble a simple example including the functions. My test was probably too simple.

Glaswegian
09-17-2009, 06:49 AM
I think I have found the problem.

One cell contains a PMT function formula - this cell was then copied to another sheet and the copy cell was the ChangeCell. I don't think Goalseek appreciated the fact that the value of the ChangeCell was dependent on 2 separate formulae. Having removed the PMT function (since Goalseek effectively provides the same answer) and made some minor tweaks, it would appear to working well now.

Thanks for your time Malcolm.