Solver Problems Calculate 1 out of 3 problems

drmingle

Board Regular
Joined
Oct 5, 2009
Messages
229
All these solver problems are on the same worksheet. I am attempting to solve them sequentially and independent of each other. However, the 1st of 3 works.... the 2nd and 3rd do not...and I get no error.

Any help would be appreciated...

Code:
Sub Calc_MultiServDollar()
Application.ScreenUpdating = False
    With Worksheets("Pricer_MultiServiceDollar")
    'Service Line 1
        Application.Run "SolverReset"
        Application.Run "SolverOK", "E9", _
                 3, _
                 Range("E8").Value, _
                 "C9"
        Application.Run "SolverSolve", True
      End With
Call Srv2
Call Srv3
Application.ScreenUpdating = True
End Sub
Code:
Sub Srv2()
Application.ScreenUpdating = False
    With Worksheets("Pricer_MultiServiceDollar")
    'Service Line 2
        Application.Run "SolverReset"
        Application.Run "SolverOK", "K9", _
                 3, _
                 Range("K8").Value, _
                 "I9"
        Application.Run "SolverSolve", True
        Application.ScreenUpdating = True
    End With
End Sub
Code:
Sub Srv3()
Application.ScreenUpdating = False
    With Worksheets("Pricer_MultiServiceDollar")
    'Service Line 3
        Application.Run "SolverReset"
        Application.Run "SolverOK", "Q9", _
                 3, _
                 Range("Q8").Value, _
                 "O9"
        Application.Run "SolverSolve", True
        Application.ScreenUpdating = True
    End With
End Sub
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,736
Office Version
2010
Platform
Windows
Maybe ...
Code:
Sub CalcMultiServDollar()
    Dim cell        As Range
 
    Worksheets("Pricer_MultiServiceDollar").Select
 
    For Each cell In Range("E9,K9,Q9")
        SolverReset
        SolverOK SetCell:=cell.Address, _
                 MaxMinVal:=3, _
                 Valueof:=cell.Offset(-1).Value, _
                 ByChange:=cell.Offset(, -2).Address
        SolverSolve UserFinish:=True
    Next cell
End Sub
This is one of the few cases where you need to select the worksheet.

If the code fails, you need to ensure that the SetCell has a dependency on the changing cell in each case.
 

drmingle

Board Regular
Joined
Oct 5, 2009
Messages
229
I appreciate the response...

However, I am still not getting normal results.

I think the issue is that Solver is attempting the presented problems, but it responds with "Solver could not find a feasible solution". I am still not sure why...

When I take the exact problem off the multiproblem worksheet (which again are not dependent on one another) the expected result is correct and I do not receive a mesage of "solver could not find a feasible solution".

In sum, I can't get the correct result when I have 3 problems on the same sheet, but if I put each problem on it's own worksheet I get the result I am looking for...

Any ideas?
 

drmingle

Board Regular
Joined
Oct 5, 2009
Messages
229
I should have mentioned that I tested this by changing the following:

From this:
Code:
 SolverSolve UserFinish:=[B][COLOR=royalblue]True[/COLOR][/B]
To this:
Code:
SolverSolve UserFinish:=[B][COLOR=royalblue]False[/COLOR][/B]
As you can see the first one calcs fine, but the other two all run into the fact that solver cannot find a solution.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,736
Office Version
2010
Platform
Windows
As you can see the first one calcs fine ...
How would I see that?

You can put the workbook on box.net or similar and post a link.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,247
Messages
5,413,289
Members
403,472
Latest member
ArtisticOwl

This Week's Hot Topics

Top