Parameter Relationship with VBA Solver Loop

**mea23** · 03-02-2015, 03:27 PM

Parameter Relationship.xlsx

In the above workbook I need to build out a loop that will work solver through the calculation for all 197 rows. The variables n (column D) and m (column E) would change in solver so that the Formula in column F (which includes these variables) equals the formula in column C (which has the same other variables, Mass and Term, but not m and n).

If anybody has any suggestions on how this can be done quickly and efficiently that would be great.

Thanks

M

**MrShorty** · 03-02-2015, 03:45 PM

equation: a*(n-1)+b*(m-1)=a*b --> a*(n-1)+b*(m-1)-a*b=0

with 1 equation in 2 unkowns, there will not be a single unique solution. for example, I get n=1.776 and m=1.707 for row 4.

I would probably solve this kind of problem algebraically first. Dust off your algebra skills and solve for n as a function of m (or m as a function of n). Then, you will be able to choose a reasonable value for m and compute the corresponding n. Such a formula could easily be copied down as far as needed. No need for Solver at all.

If there are other criteria that need to be considered to narrow down the possible solutions, then you can take those into account in your choice of m.

**Alf** · 03-02-2015, 06:43 PM

Perhaps like this? To test run macro "SolvLoop"

Before you run the macro you must set a reference to solver in Visual Basic.

Developers tab -> Visual Basic -> Tools -> References and tick box "Solver"

Alf