Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 367
Default GoalSeek 1004 Error

I am trying to use the GoalSeek function in my VBA, but am unable to get it
to work, I have been getting 1004 error

Here is the line that seems to cause the 1004 error

Worksheets("Data Sheet").Cells(1, 203).GoalSeek Goal:=Worksheets("Data
Sheet").Cells(1, 201).Value, _
Changingcell:=Worksheets("Data Sheet").Cells(1, 202)

Please let me know if you need more info.

Thank you,
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,533
Default GoalSeek 1004 Error

Hi

Nothing seems to be wrong with the line.

What is the error description more than error 1004 ?

Do you have the formula etc. in row 1 column 201:203 ?

Hopes this helps.
....
Per

"Jason" skrev i meddelelsen
...
I am trying to use the GoalSeek function in my VBA, but am unable to get it
to work, I have been getting 1004 error

Here is the line that seems to cause the 1004 error

Worksheets("Data Sheet").Cells(1, 203).GoalSeek Goal:=Worksheets("Data
Sheet").Cells(1, 201).Value, _
Changingcell:=Worksheets("Data Sheet").Cells(1, 202)

Please let me know if you need more info.

Thank you,


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 367
Default GoalSeek 1004 Error

Well that brings up a good question. I was writing the value of a formula
from VBA to that Cell instead of putting the formula itself in the cell

Is it possible to use GoalSeek without referencing a formula in a cell and
instead referencing a formula in VBA?

Example:
Y_F=TestVar1+TestVar1^2...
And use GoalSeek to try and get the formula Y_F down to zero by changing
TestVar1?



"Per Jessen" wrote:

Hi

Nothing seems to be wrong with the line.

What is the error description more than error 1004 ?

Do you have the formula etc. in row 1 column 201:203 ?

Hopes this helps.
....
Per

"Jason" skrev i meddelelsen
...
I am trying to use the GoalSeek function in my VBA, but am unable to get it
to work, I have been getting 1004 error

Here is the line that seems to cause the 1004 error

Worksheets("Data Sheet").Cells(1, 203).GoalSeek Goal:=Worksheets("Data
Sheet").Cells(1, 201).Value, _
Changingcell:=Worksheets("Data Sheet").Cells(1, 202)

Please let me know if you need more info.

Thank you,


.

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,533
Default GoalSeek 1004 Error

No, GoalSeek require a range object as formula reference.

The below is from the xl2000 helpfile.

"Calculates the values necessary to achieve a specific goal. If the goal is
an amount returned by a formula, this calculates a value that, when supplied
to your formula, causes the formula to return the number you want. Returns
True if the goal seek is successful.

Syntax

expression.GoalSeek(Goal, ChangingCell)

expression Required. An expression that returns a Range object. Must be a
single cell.
Goal Required Variant. The value you want returned in this cell.
ChangingCell Required Range. Specifies which cell should be changed to
achieve the target value."

Regards,
Per

"Jason" skrev i meddelelsen
...
Well that brings up a good question. I was writing the value of a
formula
from VBA to that Cell instead of putting the formula itself in the cell

Is it possible to use GoalSeek without referencing a formula in a cell and
instead referencing a formula in VBA?

Example:
Y_F=TestVar1+TestVar1^2...
And use GoalSeek to try and get the formula Y_F down to zero by changing
TestVar1?



"Per Jessen" wrote:

Hi

Nothing seems to be wrong with the line.

What is the error description more than error 1004 ?

Do you have the formula etc. in row 1 column 201:203 ?

Hopes this helps.
....
Per

"Jason" skrev i meddelelsen
...
I am trying to use the GoalSeek function in my VBA, but am unable to get
it
to work, I have been getting 1004 error

Here is the line that seems to cause the 1004 error

Worksheets("Data Sheet").Cells(1, 203).GoalSeek Goal:=Worksheets("Data
Sheet").Cells(1, 201).Value, _
Changingcell:=Worksheets("Data Sheet").Cells(1, 202)

Please let me know if you need more info.

Thank you,


.


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,440
Default GoalSeek 1004 Error

Maybe this is of some help, although it's just an example

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


' ================================================== =================
Function Backward(ValueToBeFound As Double, MoreArguments As Double, _
Optional ReasonableGuess, Optional MaxNumberIters, _
Optional MaxDiffPerc) As Double
€˜
€˜ Niek Otten, March 22 2006
€˜
' This EXAMPLE function goalseeks another function,
' called Forward. It works for almost any continuous function,
' although if that function has several maximum and/or minimum
' values, the value of the ReasonableGuess argument becomes
' important.
' It calculates the value for ReasonableGuess and for
' 1.2 * ReasonableGuess.
' It assumes that the function's graph is a straight line and
' extrapolates that line from these two values to find the value
' for the argument required to achieve ValueToBeFound.
' Of course that doesn't come out right, so it does it again for
' this new result and one of the other two results, depending on
' the required direction (greater or smaller).
' This process is repeated until the maximum number of calculations
' has been reached, in which case an errorvalue is returned,
' or until the value found is close enough, in which case
' the value of the most recently used argument is returned

Dim LowVar As Double, HighVar As Double, NowVar As Double
Dim LowResult As Double, HighResult As Double, NowResult As Double
Dim MaxDiff As Double
Dim NotReadyYet As Boolean
Dim IterCount As Long

If IsMissing(ReasonableGuess) Then ReasonableGuess = 1.5 ' use default
Values
If IsMissing(MaxNumberIters) Then MaxNumberIters = 20 ' that make sense in
the
If IsMissing(MaxDiffPerc) Then MaxDiffPerc = 0.001 ' context of the function

MaxDiff = ValueToBeFound * MaxDiffPerc
NotReadyYet = True
IterCount = 1
LowVar = ReasonableGuess
LowResult = Forward(LowVar, MoreArguments)
HighVar = LowVar * 1.2
HighResult = Forward(HighVar, MoreArguments)

While NotReadyYet
IterCount = IterCount + 1
If IterCount MaxNumberIters Then
Backward = CVErr(xlErrValue) 'or some other errorvalue
Exit Function
End If

NowVar = ((ValueToBeFound - LowResult) * (HighVar - LowVar) + LowVar _
* (HighResult - LowResult)) / (HighResult - LowResult)
NowResult = Forward(NowVar, MoreArguments)
If NowResult ValueToBeFound Then
HighVar = NowVar
HighResult = NowResult
Else
LowVar = NowVar
LowResult = NowResult
End If
If Abs(NowResult - ValueToBeFound) < MaxDiff Then NotReadyYet = False
Wend

Backward = NowVar

End Function
' ================================================== =================

"Jason" wrote in message
...
Well that brings up a good question. I was writing the value of a
formula
from VBA to that Cell instead of putting the formula itself in the cell

Is it possible to use GoalSeek without referencing a formula in a cell and
instead referencing a formula in VBA?

Example:
Y_F=TestVar1+TestVar1^2...
And use GoalSeek to try and get the formula Y_F down to zero by changing
TestVar1?



"Per Jessen" wrote:

Hi

Nothing seems to be wrong with the line.

What is the error description more than error 1004 ?

Do you have the formula etc. in row 1 column 201:203 ?

Hopes this helps.
....
Per

"Jason" skrev i meddelelsen
...
I am trying to use the GoalSeek function in my VBA, but am unable to get
it
to work, I have been getting 1004 error

Here is the line that seems to cause the 1004 error

Worksheets("Data Sheet").Cells(1, 203).GoalSeek Goal:=Worksheets("Data
Sheet").Cells(1, 201).Value, _
Changingcell:=Worksheets("Data Sheet").Cells(1, 202)

Please let me know if you need more info.

Thank you,


.




  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,440
Default GoalSeek 1004 Error

Watch the unintentional line breaks

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Niek Otten" wrote in message
...
Maybe this is of some help, although it's just an example

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


' ================================================== =================
Function Backward(ValueToBeFound As Double, MoreArguments As Double, _
Optional ReasonableGuess, Optional MaxNumberIters, _
Optional MaxDiffPerc) As Double
€˜
€˜ Niek Otten, March 22 2006
€˜
' This EXAMPLE function goalseeks another function,
' called Forward. It works for almost any continuous function,
' although if that function has several maximum and/or minimum
' values, the value of the ReasonableGuess argument becomes
' important.
' It calculates the value for ReasonableGuess and for
' 1.2 * ReasonableGuess.
' It assumes that the function's graph is a straight line and
' extrapolates that line from these two values to find the value
' for the argument required to achieve ValueToBeFound.
' Of course that doesn't come out right, so it does it again for
' this new result and one of the other two results, depending on
' the required direction (greater or smaller).
' This process is repeated until the maximum number of calculations
' has been reached, in which case an errorvalue is returned,
' or until the value found is close enough, in which case
' the value of the most recently used argument is returned

Dim LowVar As Double, HighVar As Double, NowVar As Double
Dim LowResult As Double, HighResult As Double, NowResult As Double
Dim MaxDiff As Double
Dim NotReadyYet As Boolean
Dim IterCount As Long

If IsMissing(ReasonableGuess) Then ReasonableGuess = 1.5 ' use default
Values
If IsMissing(MaxNumberIters) Then MaxNumberIters = 20 ' that make sense in
the
If IsMissing(MaxDiffPerc) Then MaxDiffPerc = 0.001 ' context of the
function

MaxDiff = ValueToBeFound * MaxDiffPerc
NotReadyYet = True
IterCount = 1
LowVar = ReasonableGuess
LowResult = Forward(LowVar, MoreArguments)
HighVar = LowVar * 1.2
HighResult = Forward(HighVar, MoreArguments)

While NotReadyYet
IterCount = IterCount + 1
If IterCount MaxNumberIters Then
Backward = CVErr(xlErrValue) 'or some other errorvalue
Exit Function
End If

NowVar = ((ValueToBeFound - LowResult) * (HighVar - LowVar) + LowVar _
* (HighResult - LowResult)) / (HighResult - LowResult)
NowResult = Forward(NowVar, MoreArguments)
If NowResult ValueToBeFound Then
HighVar = NowVar
HighResult = NowResult
Else
LowVar = NowVar
LowResult = NowResult
End If
If Abs(NowResult - ValueToBeFound) < MaxDiff Then NotReadyYet = False
Wend

Backward = NowVar

End Function
' ================================================== =================

"Jason" wrote in message
...
Well that brings up a good question. I was writing the value of a
formula
from VBA to that Cell instead of putting the formula itself in the cell

Is it possible to use GoalSeek without referencing a formula in a cell
and
instead referencing a formula in VBA?

Example:
Y_F=TestVar1+TestVar1^2...
And use GoalSeek to try and get the formula Y_F down to zero by changing
TestVar1?



"Per Jessen" wrote:

Hi

Nothing seems to be wrong with the line.

What is the error description more than error 1004 ?

Do you have the formula etc. in row 1 column 201:203 ?

Hopes this helps.
....
Per

"Jason" skrev i meddelelsen
...
I am trying to use the GoalSeek function in my VBA, but am unable to
get it
to work, I have been getting 1004 error

Here is the line that seems to cause the 1004 error

Worksheets("Data Sheet").Cells(1, 203).GoalSeek Goal:=Worksheets("Data
Sheet").Cells(1, 201).Value, _
Changingcell:=Worksheets("Data Sheet").Cells(1, 202)

Please let me know if you need more info.

Thank you,

.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
run time error 1004 general odbc error excel 2003 vba Mentos Excel Programming 5 January 24th 11 03:56 PM
Error 1004 opening workbook 1004 WembleyBear Excel Programming 2 November 30th 09 02:33 PM
Error when cell A1 is not active and xlInsideVertical border formatthrowing error 1004 [email protected] Excel Programming 7 August 7th 08 08:43 PM
run-time error '1004': Application-defined or object-deifined error [email protected] Excel Programming 5 August 10th 05 09:39 PM
"GoalSeek method of Range object failed" error message Fixit_Steve Excel Programming 0 January 13th 05 08:29 PM


All times are GMT +1. The time now is 11:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"