solver [This iterative problem doesn't always work with Solver; can it be solved with VBA]

I am doing repetitive calculations in excel on about 150 rows of data; the data is pressure measured over time. the equation i solve is:

h=w+(transducer depth+w)*w''/9.81

w is water level change which is WL1-WL2; WL1 is given

I match the solution with the data I collected, h using solver to change WL2 until the solution matches my measured data. sometimes solver doesn't work and returns a large number for WL2; then I manually change it until it matches. this is very time consuming and inefficient. is there a way to program in VB to accomplish this with or without solver? attached is an example file. i have a ton of files to dothanks

I don't know how to solve for row 2. For others, here is how to do it.

I note threads where I participate. As such, I see that you cross-posted this and it had no response either.

e.g. For row 3:

'http://www.excelguru.ca/forums/showthread.php?7156-solver
'http://www.excelforum.com/showthread.php?t=1167981

'https://msdn.microsoft.com/en-us/library/office/ff838657.aspx
'Tools > References > Solver  'Must enable Solver add-in first, then add reference.
Sub mSolver()
  SolverReset
  SolverOk SetCell:="$P$3", MaxMinVal:=1, ValueOf:=0, ByChange:="$L$3", Engine:=1 _
    , EngineDesc:="GRG Nonlinear"
  'Relation=2=equals
  SolverAdd CellRef:="$P$3", Relation:=2, FormulaText:="$K$3"
  ' Do not display the Solver Results dialog box.
  SolverSolve UserFinish:=False
End Sub

Thanks! and sorry, i had no idea about cross posting. i wont do it again....

MrShorty, there is no square term so it is not a quadratic equation.

The only way that I know to solve row 2 is to copy the formula from row 3. Doing that, does change all the w and WL values. I think that is the right way to go though.

Dim glb_origCalculationMode As Integer

Sub Main()
  Dim c As Range, r&
  
  SpeedOn
  On Error GoTo EndSub
  
  For Each c In Range("A2", Range("A2").End(xlDown))
    r = c.Row
    mSolver Cells(r, "P").Address, Cells(r, "L").Address, Cells(r, "K").Address
  Next c
  
EndSub:
  SpeedOff
End Sub

Sub SpeedOn(Optional StatusBarMsg As String = "Running macro...")
  glb_origCalculationMode = Application.Calculation
  With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
    .Cursor = xlWait
    .StatusBar = StatusBarMsg
    .EnableCancelKey = xlErrorHandler
  End With
End Sub

Sub SpeedOff()
  With Application
    .Calculation = glb_origCalculationMode
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
    .CalculateBeforeSave = True
    .Cursor = xlDefault
    .StatusBar = False
    .EnableCancelKey = xlInterrupt
  End With
End Sub

'https://msdn.microsoft.com/en-us/library/office/ff838657.aspx
'Tools > References > Solver  'Must enable Solver add-in first, then add reference.
Sub mSolver(sSetCell$, sByChange$, sFormulaText$)
  SolverReset
  SolverOk SetCell:=sSetCell, MaxMinVal:=1, ValueOf:=0, ByChange:=sByChange, Engine:=1 _
    , EngineDesc:="GRG Nonlinear"
  'Relation=2=equals
  SolverAdd CellRef:=sSetCell, Relation:=2, FormulaText:=sFormulaText
  ' Do not display the Solver Results dialog box?
  SolverSolve UserFinish:=True  'True=do not show, False=do show.
End Sub