Automate Excel's Solver with macro
Hi,
Using the formula for calculating the ellipsoidal distance between two points, I'm trying to extract a specific input variable value with a given distance value. I've already asked for a way to reverse the formula in order to isolate the variable I want to calculate, but unfortunately, the formula doesn't allow in this case a closed-form solution.
I resort then to Excel's Solver add-in and fortunately, it works even for very high accuracies (15 decimals). The problem is to reiterate the solver thousands of times.
Here's a screenshot of the problem.
The Distance (Column K) is the result of this formula and the Decimal (Column H) is the only input variable that determines all the other terms of the formula.
Within the Solver, the Objective is the Distance (Column K), with a set value that depends on the Dist base (Column L), but I inserted it manually. Finally, the Variable Cell is the Decimal (Column H).
I've tried to build an automatisation of this process with a step-to-step tutorial I've found online, but without the basic knowledge of VBA I'm not able to implement these steps into my case.
The only thing I've managed to do is to record the Macro of one Solver operation, as follows:
Sub SolverLoop()
'
' SolverLoop Macro
'
'
SolverOk SetCell:="$K$10", MaxMinVal:=3, ValueOf:=3.5, ByChange:="$H$10", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$K$10", MaxMinVal:=3, ValueOf:=3.5, ByChange:="$H$10", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve
End Sub
How can I implement this code in order to reiterate the Solver for the "Distance" column up to a value of 10000?
Thanks in advance!
Andrea