**Excel:**A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.

**Management:**The act or process of organizing, handling, directing or controlling something.

Skip to main content

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Andrea Galligari
1
Reputation point

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