I have trouble getting a solver call to work, but another call does
work. Let me show you what I have so far. On a form I have two solver
calls, the first one works but the second doesn't. There is no error
(at least none that I can recall) , it's just that the solver doesn't
run, meaning the cell $A$150 has the same value at the end as it had in
the beginning. Even debugging the code doesn't give me any clues. I
calculated everything by hand and I am pretty sure that there is a
valid solution for this equation.
What am I doing wrong?
The first one( this one works)

'//Iteration algorithm for determening the inner dia meter of the
expanded liner by using the Solver

'//Creating a temporary changing cell
Sheets("outputExpansion").Cells(150, 1)
=(Sheets("InputValues").Cells(13, 2) - 2 *
Sheets("InputValues").Cells(14, 2)) * 25.4

'//Creating a temporary target cell
If m_rho = (-2 / 3) Then

Sheets("outputExpansion").Cells(149, 1).Formula = "=$A$150 +
(2* InputValues!B14 * 25.4) * ($A$150 / ((InputValues!B13 - 2
*InputValues!B14))* 25.4) ^ (-2 / 3) - ((InputValues!B4 - 2 *
InputValues!B2) * 25.4)"

Else

Sheets("outputExpansion").Cells(149, 1).Formula = "=$A$150 +
(2* InputValues!B14 * 25.4) * ($A$150 / ((InputValues!B13 - 2
*InputValues!B14))* 25.4) ^ (-1) - ((InputValues!B4 - 2
*InputValues!B2) * 25.4)"

End If

'//Actual Solver procedure
Sheets("outputExpansion").Select
solver.Auto_open
SolverOk SetCell:="$A$149", MaxMinVal:=3,
ValueOf:="0",ByChange:="$A$150"
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
solver.Auto_close

'//The inner dia meter of the liner after the Solver procedure
de = Sheets("outputExpansion").Cells(150, 1)

'//Delete the temporary cells
Sheets("outputExpansion").Cells(150, 1).Delete
Sheets("outputExpansion").Cells(149, 1).Delete

'//================================================== ==========================

Now the second one, where I need to know the value of $A$150, B9 =
0.003, B7= 0.19 , B3 = 0.26, B2 = 0.28

'// Maximum expansion ratio for rho = -2/3

Sheets("outputExpansion").Cells(150, 1) = 0.0001

Sheets("outputExpansion").Cells(149, 1).Formula = "=((InputValues!B9
+$A$150)/(1.52765618*
InputValues!B7))^InputValues!B7*(2.71828182845904^ (InputValues!B7
-($A$150 + InputValues!B9)/1.52765618))- ((InputValues!B3
*25.4)/(InputValues!B2 * 25.4)) "

'//Actual Solver procedure
Sheets("outputExpansion").Select
solver.Auto_open
SolverOk SetCell:="$A$149", MaxMinVal:=3,
ValueOf:="0",ByChange:="$A$150"
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
solver.Auto_close

'//The inner dia meter of the liner after the Solver procedure
delta_exp_ratio = Sheets("outputExpansion").Cells(150, 1)

'//Delete the temporary cells
Sheets("outputExpansion").Cells(150, 1).Delete
Sheets("outputExpansion").Cells(149, 1).Delete

'//================================================== =======

Is there anyone who can help me???

Hi. I don't have a solution, just an idea.
Although not based on anything, I try to avoid Solver problems that gets
data from other sheets. I would try to move everything to one sheet.
I would try to use Range Names if possible. Note that 2.718^x = Exp(x).
I used a Constant k to represent 1.527...
It appears to me that 25.4 just cancels out. Is the equation correct?

See if this is a little easier to read.

=Exp((B7-(A150+B9)/k))*((A150+B9)/(B7*k))^B7-B3/B2

Range("Target").Formula = ...etc

vs...

Sheets("outputExpansion").Cells(149, 1).Formula = "=((InputValues!B9
+$A$150)/(1.52765618*
InputValues!B7))^InputValues!B7*(2.71828182845904^ (InputValues!B7
-($A$150 + InputValues!B9)/1.52765618))- ((InputValues!B3
*25.4)/(InputValues!B2 * 25.4)) "

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"xyfix" wrote in message
ups.com...
Is there anyone who can help me???

You're right, when you say that tha 25.4 gets cancelled out, but it is
a conversion constant (inch to millimeter) and I' ve already removed
it. I'm going to try your idea and get back to you.

xyfix

Dana DeLouis schreef:

Hi. I don't have a solution, just an idea.
Although not based on anything, I try to avoid Solver problems that gets
data from other sheets. I would try to move everything to one sheet.
I would try to use Range Names if possible. Note that 2.718^x = Exp(x).
I used a Constant k to represent 1.527...
It appears to me that 25.4 just cancels out. Is the equation correct?

See if this is a little easier to read.

=Exp((B7-(A150+B9)/k))*((A150+B9)/(B7*k))^B7-B3/B2

Range("Target").Formula = ...etc

vs...

Sheets("outputExpansion").Cells(149, 1).Formula = "=((InputValues!B9
+$A$150)/(1.52765618*
InputValues!B7))^InputValues!B7*(2.71828182845904^ (InputValues!B7
-($A$150 + InputValues!B9)/1.52765618))- ((InputValues!B3
*25.4)/(InputValues!B2 * 25.4)) "

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"xyfix" wrote in message
ups.com...
Is there anyone who can help me???

Hi. If the second equation is correct, and given your data, a math program
shows that there are two solutions for A150 that can make your equation
equal to Zero.
A150 ~ .1001 & A150 ~ .624
You may have to adjust Solver's options for Precision, Tolerance, and
Convergence.

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"xyfix" wrote in message
ups.com...
You're right, when you say that tha 25.4 gets cancelled out, but it is
a conversion constant (inch to millimeter) and I' ve already removed
it. I'm going to try your idea and get back to you.

xyfix

Dana DeLouis schreef:

Hi. I don't have a solution, just an idea.
Although not based on anything, I try to avoid Solver problems that gets
data from other sheets. I would try to move everything to one sheet.
I would try to use Range Names if possible. Note that 2.718^x = Exp(x).
I used a Constant k to represent 1.527...
It appears to me that 25.4 just cancels out. Is the equation correct?

See if this is a little easier to read.

=Exp((B7-(A150+B9)/k))*((A150+B9)/(B7*k))^B7-B3/B2

Range("Target").Formula = ...etc

vs...

Sheets("outputExpansion").Cells(149, 1).Formula = "=((InputValues!B9
+$A$150)/(1.52765618*
InputValues!B7))^InputValues!B7*(2.71828182845904^ (InputValues!B7
-($A$150 + InputValues!B9)/1.52765618))- ((InputValues!B3
*25.4)/(InputValues!B2 * 25.4)) "

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"xyfix" wrote in message
ups.com...
Is there anyone who can help me???

Just an idea. Sometimes, you can get more accuracy by using Newton's
Equation directly in your code. This isn't fancy, but I was thinking
something like this...

Sub TestIt()
Debug.Print FX(1)
Debug.Print FX(0)
End Sub

Returns:
0.624031260377961
0.10013561506815

Function FX(Guess) As Double

Dim B9, B7, B3, B2, K
Dim Num, Den
Dim J As Long
Dim X As Double

B2 = 0.28
B3 = 0.26
B7 = 0.19
B9 = 0.003
K = 1.52765618
X = Guess

For J = 1 To 12

Num=Exp((B7-(B9+X)/K))*((B9+X)/(B7*K))^B7-B3/B2

Den=((B7*((B9+X)/(B7*K))^(B7+1)*(B7*K-X-B9))/_
(Exp(((B9-B7*K+X)/K))*(B9+X)^2))

X = X - Num / Den
Next J
FX = X
End Function

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"Dana DeLouis" wrote in message
...
Hi. If the second equation is correct, and given your data, a math
program shows that there are two solutions for A150 that can make your
equation equal to Zero.
A150 ~ .1001 & A150 ~ .624
You may have to adjust Solver's options for Precision, Tolerance, and
Convergence.

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"xyfix" wrote in message
ups.com...
You're right, when you say that tha 25.4 gets cancelled out, but it is
a conversion constant (inch to millimeter) and I' ve already removed
it. I'm going to try your idea and get back to you.

xyfix

Dana DeLouis schreef:

Hi. I don't have a solution, just an idea.
Although not based on anything, I try to avoid Solver problems that gets
data from other sheets. I would try to move everything to one sheet.
I would try to use Range Names if possible. Note that 2.718^x = Exp(x).
I used a Constant k to represent 1.527...
It appears to me that 25.4 just cancels out. Is the equation correct?

See if this is a little easier to read.

=Exp((B7-(A150+B9)/k))*((A150+B9)/(B7*k))^B7-B3/B2

Range("Target").Formula = ...etc

vs...

Sheets("outputExpansion").Cells(149, 1).Formula = "=((InputValues!B9
+$A$150)/(1.52765618*
InputValues!B7))^InputValues!B7*(2.71828182845904^ (InputValues!B7
-($A$150 + InputValues!B9)/1.52765618))- ((InputValues!B3
*25.4)/(InputValues!B2 * 25.4)) "

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"xyfix" wrote in message
ups.com...
Is there anyone who can help me???