Nothing happens with range("B2").Goalseek _...

P

Phil

Could someone please tell me what I'm missing with using the goalseek
method?

A test spreadsheet taken from MSDN: GoalSeek Method [Excel 2003 VBA
Language Reference] illustrates the problem. Calling the VBA
seekgoal() function does not do anything. Here's the code, with notes:

' sheet 1 has cell b2 set to 6, named to "Polynomial"
' sheet 1 has cell b1 set to empty, named as "X"
' I call function seekgoal() from sheet1

Public Function seekgoal() As Boolean

P = Worksheets("Sheet1").Range("Polynomial") ' p is 6
Xt = Worksheets("Sheet1").Range("X") ' x is empty

Stop ' yep, were executing. range references are correct. [F8]
continues

Worksheets("Sheet1").Range("Polynomial").GoalSeek _
goal:=15, _
ChangingCell:=Worksheets("Sheet1").Range("X")
End Function

' but nothing happens: neither cell b1 nor b2 changed in sheet1. No
error messages occur.

Running Tools-Goal Seek from the spreadsheet finds a solution
(X = 1.426018623 and Polynomial = 15.00043772)

Thank you in advance. I appreciate any thoughts you have.

Phil
 
M

merjet

Change from a Function to a Sub and it should work as desired.

Public seekgoal()
.......
End Sub

Hth,
Merjet
 

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. After that, you can post your question and our members will help you out.

Ask a Question

Top