Vba goal seek

Hi. My intent is to find the value in Range("C1") when "AperC"="BperD".
"BperD" turns out to be an invalid qualifier for goal seek. I was wondering if anyone know of a work around. Thank you much.
---------------------------------------------------------------
Private Sub Worksheet_Calculate()
Dim AperC as Double
Dim BperD as Double

AperC = Range("A1")^2 / 10
BperD = Range("B1")^3+Range("C1")

On Error GoTo TheEnd
Application.EnableEvents = False

Do Until AperC=BperD
BperD.GoalSeek Goal:=AperC, _
ChangingCell:=Range("C1")
Loop

TheEnd:
Application.EnableEvents = True
End Sub

Try this:

Dim AperC As Double
Dim BperD As Range

AperC = Range("A1") ^ 2 / 10
'Set BperD = Range("B1") ^ 3 + Range("C1")
Range("D1") = "=B1^3+C1"
Set BperD = Range("D1")


On Error GoTo TheEnd
Application.EnableEvents = False

Do Until AperC = BperD
BperD.GoalSeek Goal:=AperC, ChangingCell:=Range("C1")
Loop

TheEnd:
Application.EnableEvents = True



Mangesh

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Range("A2").Formula = "=A1^2/10"
Range("B2").Formula = "=B1^3+C1"
Range("A2").Name = "AperC"
Range("B2").Name = "BperD"
Range("C2").Formula = "=AperC-BperD"
On Error GoTo TheEnd

Range("C2").GoalSeek Goal:=0, _
ChangingCell:=Range("C1")


TheEnd:
Application.EnableEvents = True
End Sub

Goalseek works on Cells.

--
Regards,
Tom Ogilvy


"csw78" <[email protected]> wrote in
message news:[email protected]...
>
> Hi. My intent is to find the value in Range("C1") when "AperC"="BperD".
> "BperD" turns out to be an invalid qualifier for goal seek. I was
> wondering if anyone know of a work around. Thank you much.
> ---------------------------------------------------------------
> Private Sub Worksheet_Calculate()
> Dim AperC as Double
> Dim BperD as Double
>
> AperC = Range("A1")^2 / 10
> BperD = Range("B1")^3+Range("C1")
>
> On Error GoTo TheEnd
> Application.EnableEvents = False
>
> Do Until AperC=BperD
> BperD.GoalSeek Goal:=AperC, _
> ChangingCell:=Range("C1")
> Loop
>
> TheEnd:
> Application.EnableEvents = True
> End Sub
>
>
> --
> csw78
> ------------------------------------------------------------------------
> csw78's Profile:
http://www.excelforum.com/member.php...o&userid=23008
> View this thread: http://www.excelforum.com/showthread...hreadid=376747
>

BperC is a VBA variable declared as double. A double is not an object
that supports properties or methods. GoalSeek is a method that applies
to a Range object.

From what it appears you want to do, add a formula in, say, D1:
=B1^3+C1-A1^2/10.

Now, use Range("D1").Goal Goal:=0,ChangingCell:=Range("C1")
--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
>
> Hi. My intent is to find the value in Range("C1") when "AperC"="BperD".
> "BperD" turns out to be an invalid qualifier for goal seek. I was
> wondering if anyone know of a work around. Thank you much.
> ---------------------------------------------------------------
> Private Sub Worksheet_Calculate()
> Dim AperC as Double
> Dim BperD as Double
>
> AperC = Range("A1")^2 / 10
> BperD = Range("B1")^3+Range("C1")
>
> On Error GoTo TheEnd
> Application.EnableEvents = False
>
> Do Until AperC=BperD
> BperD.GoalSeek Goal:=AperC, _
> ChangingCell:=Range("C1")
> Loop
>
> TheEnd:
> Application.EnableEvents = True
> End Sub
>
>
> --
> csw78
> ------------------------------------------------------------------------
> csw78's Profile: http://www.excelforum.com/member.php...o&userid=23008
> View this thread: http://www.excelforum.com/showthread...hreadid=376747
>
>

As I suspected, goal seek only works on ranges. Thanks for all your inputs.

I am looking for a way to control the number of iterations which goal seek
does. The reason why I am looking for is, I want to break-out of the loop if
my formula goes out of range.

Thanks for help

You can control the max number of iterations and the required precision in
Tools>Options, Calculation Tab.

Or do you mean something else?

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"Mahendhra" <[email protected]> wrote in message
news:[email protected]...
>I am looking for a way to control the number of iterations which goal seek
> does. The reason why I am looking for is, I want to break-out of the loop
> if
> my formula goes out of range.
>
> Thanks for help

Thanks. At each and every instant of goal seek iteration, I wan to monitor
the value (for example compare if it is less than or greater than) and break
out of the goal seek solver. Appreciate if you could suggest some VBA codes.

kind Regards,
Mahendhra

"Niek Otten" wrote:

> You can control the max number of iterations and the required precision in
> Tools>Options, Calculation Tab.
>
> Or do you mean something else?
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "Mahendhra" <[email protected]> wrote in message
> news:[email protected]...
> >I am looking for a way to control the number of iterations which goal seek
> > does. The reason why I am looking for is, I want to break-out of the loop
> > if
> > my formula goes out of range.
> >
> > Thanks for help
>
>
>

Maybe not exactly what you require, but here is a function that iterates.
Maybe you can make it suit your needs.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
' ===================================================================
Option Explicit

Function Backward(ValueToBeFound As Double, MoreArguments As Double, _
Optional ReasonableGuess, Optional MaxNumberIters, _
Optional MaxDiffPerc) As Double

' 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

Function Forward(a As Double, b As Double) As Double
' This is just an example function;
' almost any continous function will work
Forward = 3 * a ^ (1.5) + b
End Function
' ===================================================================

"Mahendhra" <[email protected]> wrote in message
news:[email protected]...
> Thanks. At each and every instant of goal seek iteration, I wan to
> monitor
> the value (for example compare if it is less than or greater than) and
> break
> out of the goal seek solver. Appreciate if you could suggest some VBA
> codes.
>
> kind Regards,
> Mahendhra
>
> "Niek Otten" wrote:
>
>> You can control the max number of iterations and the required precision
>> in
>> Tools>Options, Calculation Tab.
>>
>> Or do you mean something else?
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> Microsoft MVP - Excel
>>
>> "Mahendhra" <[email protected]> wrote in message
>> news:[email protected]...
>> >I am looking for a way to control the number of iterations which goal
>> >seek
>> > does. The reason why I am looking for is, I want to break-out of the
>> > loop
>> > if
>> > my formula goes out of range.
>> >
>> > Thanks for help
>>
>>
>>

Thanks for the code, its good for linear.

"Niek Otten" wrote:

> Maybe not exactly what you require, but here is a function that iterates.
> Maybe you can make it suit your needs.
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
> ' ===================================================================
> Option Explicit
>
> Function Backward(ValueToBeFound As Double, MoreArguments As Double, _
> Optional ReasonableGuess, Optional MaxNumberIters, _
> Optional MaxDiffPerc) As Double
>
> ' 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
>
> Function Forward(a As Double, b As Double) As Double
> ' This is just an example function;
> ' almost any continous function will work
> Forward = 3 * a ^ (1.5) + b
> End Function
> ' ===================================================================
>
> "Mahendhra" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks. At each and every instant of goal seek iteration, I wan to
> > monitor
> > the value (for example compare if it is less than or greater than) and
> > break
> > out of the goal seek solver. Appreciate if you could suggest some VBA
> > codes.
> >
> > kind Regards,
> > Mahendhra
> >
> > "Niek Otten" wrote:
> >
> >> You can control the max number of iterations and the required precision
> >> in
> >> Tools>Options, Calculation Tab.
> >>
> >> Or do you mean something else?
> >>
> >> --
> >> Kind regards,
> >>
> >> Niek Otten
> >>
> >> Microsoft MVP - Excel
> >>
> >> "Mahendhra" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I am looking for a way to control the number of iterations which goal
> >> >seek
> >> > does. The reason why I am looking for is, I want to break-out of the
> >> > loop
> >> > if
> >> > my formula goes out of range.
> >> >
> >> > Thanks for help
> >>
> >>
> >>
>
>
>