Automate Excel's Solver with macro

Andrea Galligari 1 Reputation point
2021-11-16T15:01:16.813+00:00

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.

8rLQ0Rc

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

Excel Management
Excel Management
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.
1,348 questions
No comments
{count} votes