Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
Error 1004 opening workbook 1004 | Excel Programming | |||
Error when cell A1 is not active and xlInsideVertical border formatthrowing error 1004 | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
"GoalSeek method of Range object failed" error message | Excel Programming |