Solving for a variable multiple times

[Guest]

Hey all,

I'm creating a worksheet that helps me determine water depth in a lagoon. I
want to calculate the water depth from month to month for 12 years (144
Calculations of depth). The formula is Volume = X*Y*H +18*h^3+3*X*H^2
+3*Y*H^2

X=lagoon floor depth (user defined)
Y=lagoon floor width (user defined)
H= water depth

The volume changes each month due to evaporation, seepage, and inflow. My
spread sheet calculates the volume at the end of the month. The next step
would be to solve for H using the formula above.

I can use goal seek to solve for H starting at the first month and then
doing the same for the following month, but I don't want to do this 144
times. I created a macro that automates the goal seek routine for each
month, but how do I automate the macro to do this for every month . I need
the macro to look at the next row down and repeat the goal seek routine until
it hits the end of the worksheet.

Any help is appreciated.

 

[Rob van Gelder]

Without seeing your code I can't give you an exact solution.

My approach would be to create your "goalseek automation" as a sub procedure
which takes a rownumber as a parameter.

Then just call the procedure 144 times:

For i = 1 To 144
GoalSeekH i
Next

 

[Dana DeLouis]

Is small "h" a different variable than "H" ? (as in 18*h^3).

My
spread sheet calculates the volume at the end of the month.

Are you given "H" to begin with in order for the equation to calculate
Volume? Or do you mean that you are given the Volume by some other means,
and you wish to calculate 'H', the water depth?

 

[Slick Willie]

Hows 'bout this?
count = 0
intNumRows = Worksheets("Sheet1").UsedRange.Rows.Count
While counter < IntNumRows
var = yourFormula
count = count + 1
Cells(count,"D") = var
Wend

 

[Dana DeLouis]

I may have it wrong because it's a cubic equation, but see if this custom
function might work instead of Goal Seek. I assume 'h' is the same as "H",
and that you are given Volume, x,y, and wish to find H.

Function WaterDepth(V, x, y)
Dim t1, t2
t1=x^2+y^2-7*x*y
t2=(162*V-t1*(x+y)+3*Sqr(3)*Sqr(972*V^2-12*t1*V*(x+y)-x^2*y^2*(t1+x*y)))^(1/3)
WaterDepth=(t1+t2^2+3*x*y-t2*(x+y))/(18*t2)
End Function

HTH