Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Solver iterations

I thought my Solver would give me the end result by just running it once but I noticed that I need to run this code (without the for loop in it) at least 5 times to get the best results.

Is there a reason why I have to run this several times? My workaround was the for loop equating to clicking the command button 10 times

For i = 1 To 10

SolverOk SetCell:="$B$7", MaxMinVal:=2, ValueOf:="0", ByChange:="$B$16:$I$16"
SolverOptions MaxTime:=100, Iterations:=1000, Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.001, AssumeNonNeg:=False
SolverOk SetCell:="$B$7", MaxMinVal:=2, ValueOf:="0", ByChange:="$B$16:$I$16"
'SolverSolve

SolverSolve UserFinish:=True

SolverFinish KeepFinal:=1

If i = 10 Then Exit Sub
Next


Post your answer or comment

comments powered by Disqus
Hello,

I have a written a small sub to run solver. However, I need to run a secondary sub at each solver iteration.

The secondary sub I want to run is called “BypassGoal”.

My code currently looks like:

	VB:
	
 OperatingPt() 
     
    Dim WrkBk As Workbook 
    Dim WrkSht1 As Worksheet 
     
    Set WrkBk = ThisWorkbook 
    Set WrkSht1 = WrkBk.Worksheets("Engine") 
    Dim Mode As String 
    Dim CellTarget As String 
    Dim CellRange As String 
     
     
    WrkSht1.Activate 
     
    WrkSht1.Cells(30, 2).Value = 2 
    CellTarget = "H14" 
    CellRange = "B30" 
     
     
    SolverReset 
    SolverOptions Iterations:=3000, Precision:=0.000001, AssumeNonNeg:=True, Derivatives:=2, StepThru:=True 
    SolverOk SetCell:=CellTarget, MaxMinVal:=2, ByChange:=CellRange 
    SolverSolve UserFinish:=True, ShowRef:="BypassGoal" 
    SolverSolve (True) 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem I have is that the secondary macro is only run on the first iteration. Also, if I don’t have the “SolverSolve (True)” portion then solver won’t display the stepthru window, and without this window solver won’t continue to the next iteration.

I have two questions,

1) How do you get solver to run the secondary sub routine for each interation?
2) How do you get solver to stepthru each iteration without user interaction?

TIA

EDIT: The correct name for the secondary sub was "BypassGoal" not "Bypass".

Hi,
I'm trying to make a macro that will perform multiple iterations of solver, paste each individual answer to another part of the sheet. If I go to solver and set:
Set Objective: $C$9
To: Max
By Changing Variable Cells: $C$6
Subject to the Constraints: $C$6

In Excel 2004 you could call the Solver in a loop from VBA, and Solver commands were synchronous so this would work; each call would finish before the next iteration started.

In Excel 2011 this is broken: You can call the solver iteratively, but each call returns immediately before Solver has finished (before it's launched, actually), so the next iteration starts immediately and clobbers the problem setup. Example: In the attached code, call the Fit macro on the cell that's active (BV7) when the sheet opens. The code loops quickly down through the five models and stops on the first empty cell, as it's supposed to -- but only the model on the last row gets solved.

Calling Wait after calling Solver doesn't help, as that seems to block the Solver as well as Excel. The Solver routines also don't seem to have a Busy property I can test.

Bottom line: In Excel 2011, code that iteratively calls Solver doesn't work.

Anyone have a suggestion? I've contacted Microsoft (paid tech support request), but they've gone dark.

Anyone know if this works on Excel 2010 on a PC? The people at Frontline seem to think the VBA implementation there is more reliable, but haven't been able/willing to give me a specific answer to this question.

Time to move to Matlab?

Hi All,

I have read through this forum regarding this topic and have had no luck implementing a fix. What I need is, that at the end of each iteration of a solver instance, a macro is called. Each iteration should call the function "Macro1". As it is now, it appears that the solver is called, but the macro is not. My code is below:

Function Macro1()
Range("aq2").GoalSeek Goal:=0, ChangingCell:=Range("target")
Range("target").Copy
Range("as2").PasteSpecial Paste:=xlPasteValues
End Function

Sub SolveForSituations()
Dim sRangeName As String, sRangeAddress As String
Dim nResult As Long, nCalcMode As Long
sRangeName = "EscenariosSolverByChange"
sRangeAddress = Range(sRangeName).Address
Range("SolverResult").Clear
Range("ag2").Clear
Range("ai2").Clear
Range(sRangeName).Value = 25
solverReset
SolverOk SetCell:=Range("SolverTarget").Address, MaxMinVal:=2, ValueOf:="0", ByChange:= _
Range("EscenariosSolverByChange").Address
SolverAdd CellRef:=sRangeAddress, Relation:=1, FormulaText:="75"
SolverAdd CellRef:=sRangeAddress, Relation:=3, FormulaText:="-75"
SolverOptions StepThru:=True
SolverSolve UserFinish:=True, ShowRef:=Macro1
End Sub

Note: This is a repost. Old post had MAJOR formatting issues.

I want to use Solver to optimize a complex model that
has been coded into VBA. The model cannot be converted
to a UDF because of its complexity (including Fortran
code calls). FYI: the model represents reservoir operations
for a river system in California.

Macro "SolveEq" uses a simple 2D equation as its output
function. It works fine (finds minima).

Macro "SolveF" was designed to do the same thing with one
exception: it uses the equation in Macro "f" as its
objective function.

Macro "f" works fine by itself.

When I execute "SolveF" it does not call Macro "f".
Checked and rechecked StepThru:=True and ShowRef:="f". No good.

I've trolled the forums and MS help system. Have simplified
the code to try to crystalize the issues.

Suggestions? Please?

Spreadsheet description:

Input Range: B3:B4
Output by Equation: C3 (Equation: =10+2*B3+0.5*B3^2+2*B4+0.5*B4^2)
Output by Macro "f": D3
Iteration counter: E3


	VB:
	
VBA Module1: 
 
Option Explicit 
 
Sub SolveEq() 
     'Purpose: Operate [Solver] using cell equation as the objective function
     'Output stored in cell C10 (Output by Equation)
     '
    SolverOptions precision:=0.001, StepThru:=False 
    SolverOK SetCell:=Range("c3"), MaxMinVal:=2, ByChange:=Range("B3:B4") 
    SolverSolve UserFinish:=False 
     '
End Sub 
 
 
Sub SolveF() 
     'Purpose: Operate [Solver] using Macro "f" as the objective function
     'Output stored in cell D10 (Output by Macro "f"
     '
    SolverOptions StepThru:=True 
    SolverOK SetCell:=Range("d3"), MaxMinVal:=2, ByChange:=Range("B3:B4") 
    SolverSolve UserFinish:=False, ShowRef:="f" 
     '
End Sub 
 
 
Sub f() 
     'This Macro does the same computation as in cell C3 (Output by Equation)
     'and puts the answer into cell D3 (Output by Macro "f")
     
    Dim x, y 
     
    x = Range("b3").Value 
    y = Range("b4").Value 
    Range("d3").Value = 10 + 2 * x + 0.5 * x ^ 2 + 2 * y + 0.5 * y ^ 2 
     
     'Update iteration counter
    Range("e3").Value = Range("e3").Value + 1 
     
End Sub 
 
 'End of Module1

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have to apply Solver in a loop, fitting one model per row, for several hundred rows. Each fit takes a couple of seconds.

I have VBA code (attached) that worked in Excel 2004 for Mac. In Excel 2011 for Mac, the code runs, but the Solver doesn't run to completion on each row. I think what's happening is that the call that launches Solver returns before Solver is finished (the call doesn't "block") and the next iteration resets it. In the end, the model is only fit to the last row. I think a few other users have had a similar problem but I haven't seen a solution.

A reduced-size spreadsheet and VBA code are attached. Run the code with BV7 selected (should be selected on launch). The model parameters are on the same row as the target cell, in the columns to the left (BR to BU). The code will loop through the three rows but will fit the model only on the last row.

Hy folks,

i am using the solver in VBA. I want to know the number of interactions used and if the solver found a solution or not. But the solverget(10) shows me the number i defined in Iterations.
Solverget doesn't appears to be the better thing to do.

I am using the code above:


	VB:
	
 teste2() 
    For cont = 2 To 4 
        SolverReset 
        SolverOk SetCell:="$F$" & cont, MaxMinVal:=2, ValueOf:="0", ByChange:="$B$" & cont & ":$D$" & cont 
        SolverAdd CellRef:="$B$" & cont, Relation:=1, FormulaText:="1" 
        SolverAdd CellRef:="$C$" & cont, Relation:=1, FormulaText:="1" 
        SolverAdd CellRef:="$D$" & cont, Relation:=1, FormulaText:="1" 
        SolverAdd CellRef:="$B$" & cont, Relation:=3, FormulaText:="0" 
        SolverAdd CellRef:="$C$" & cont, Relation:=3, FormulaText:="0" 
        SolverAdd CellRef:="$D$" & cont, Relation:=3, FormulaText:="0" 
        SolverOptions MaxTime:=100, Iterations:=32000, Precision:=0.000001, _ 
        AssumeLinear:=False, StepThru:=True, Estimates:=1, Derivatives:=1, _ 
        SearchOption:=1, IntTolerance:=5, Scaling:=False, Convergence:=0.0001, _ 
        AssumeNonNeg:=False 
        SolverSolve UserFinish:=True, showref:=inter(cont) 
         
    Next cont 
     
End Sub 
Function inter(cont) 
     
    Range("$G$" & cont).Value = solverget(10) 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks a lot! Best Regards

HI

When using solver and in particular the iterations it works through, I thought it would be impressive if i was able to see the changes on the worksheet as it went through rather than showing the final result.

Is there a way to force it to show each option its going through?

Hi everybody, Im new here.

Im developing a software in Excel and I want an animated gif to be visible and running while the Solver is iterating to resolve a quite big algebraic equation system. I have already put an animated gif using a Web Browser Object inside a "select WorkSheet" event, and everything is OK if the Solver isnt working. While the Solver is iterating, only a white space can be seen instead of the desirated animation. Do you have any suggestions?

Thanks in advance.

I've written an iterative numerical integration Sub which works fine. Several arguments are passed to it (cell references).

I now wish to incorporate Solver in the code process so that it reads the integration result, changes the values of two of the argument cells, after which the integration routine runs again, Solver changes the cells again, etc, until the integral is minimised.

Overall:

1. Initial values are set up as the arguments.

2. The integration routine then iteratively calculates the integral, based on the passed arguments.

3. Solver reads the integration result, then changes two of the argument cells.

4. Steps 2 and 3 repeat until Solver determines the integral result found at step 2 has been minimised.

(The final values of the changing cells (arguments) are then used for other things.)

I am unable to define the structure of the VBA code to achieve this.

I call the integration Sub, then call Solver, but Solver does a single pass only.

How do I get the integration sub and Solver to be repeatedly called, until the minimum integral value is found, after which the result is passed back to a cell, and the code terminates?

I would like to set up my macro so that if Solver does not converge at
maximum iterations, the Solver dialog is not displayed, and the macro
continues on with the best guess. Is there a way to turn off the dialog if
Solver doesn't converge?

Hello,

This is my first post, although I have been using this forum for a few months to help me in learning VBA code. I have written some code that is trying to use the solver functionality in Excel in order to minimize an objective function by changing several parameters and subjecting them to some constraints. It is a mixed binary/ non linear objective function.

The code I have thus far can be found below. When I try to run the macro I recieve no errors, however none of the cells I try to change are actually being changed at all.


	VB:
	
 
Sub MacroSolve() 
    Dim RowCount As String 
    Dim i As Integer 
    Worksheets("Sheet1").Activate 
    RowCount = 2 
    Do While Not IsEmpty(Worksheets("Sheet1").Range("A" & RowCount)) 
        SolverReset 
        SolverOptions Precision:=0.00001 
        SolverOptions MaxTime:=1000 
        SolverOptions Iterations:=1000 
        SolverOk SetCell:=Range("B" & RowCount), _ 
        MaxMinVal:=2, _ 
        ByChange:="ActiveCell.Offset(RowCount, 4), ActiveCell.Offset(RowCount, 5), ActiveCell.Offset(RowCount, 8),
ActiveCell.Offset(RowCount, 9), ActiveCell.Offset(RowCount, 12), ActiveCell.Offset(RowCount, 13), ActiveCell.Offset(RowCount,
18), ActiveCell.Offset(RowCount, 19), ActiveCell.Offset(RowCount, 23), ActiveCell.Offset(RowCount, 24),
ActiveCell.Offset(RowCount, 28), ActiveCell.Offset(RowCount, 29), ActiveCell.Offset(RowCount, 33),
ActiveCell.Offset(RowCount, 34), ActiveCell.Offset(RowCount, 38), ActiveCell.Offset(RowCount, 39),
ActiveCell.Offset(RowCount, 43), ActiveCell.Offset(RowCount, 44), ActiveCell.Offset(RowCount, 48),
ActiveCell.Offset(RowCount, 49), ActiveCell.Offset(RowCount, 53), ActiveCell.Offset(RowCount, 54),
ActiveCell.Offset(RowCount, 57), ActiveCell.Offset(RowCount, 58), ActiveCell.Offset(RowCount, 62),
ActiveCell.Offset(RowCount, 63)" 
        SolverAdd CellRef:=Range("BY" & RowCount), _ 
        Relation:=3, _ 
        FormulaText:=0 
        SolverAdd CellRef:=Range("BY" & RowCount), _ 
        Relation:=1, _ 
        FormulaText:=900 
        SolverAdd CellRef:=Range("BZ" & RowCount), _ 
        Relation:=3, _ 
        FormulaText:=0 
        SolverAdd CellRef:=Range("BZ" & RowCount), _ 
        Relation:=1, _ 
        FormulaText:=900 
        SolverAdd CellRef:=Range("CA" & RowCount), _ 
        Relation:=3, _ 
        FormulaText:=0 
        SolverAdd CellRef:=Range("CA" & RowCount), _ 
        Relation:=1, _ 
        FormulaText:=900 
        SolverAdd CellRef:=Range("CB" & RowCount), _ 
        Relation:=3, _ 
        FormulaText:=0 
        SolverAdd CellRef:=Range("CB" & RowCount), _ 
        Relation:=1, _ 
        FormulaText:=1029.3 
        SolverAdd CellRef:=Range("CC" & RowCount), _ 
        Relation:=3, _ 
        FormulaText:=0 
        SolverAdd CellRef:=Range("CC" & RowCount), _ 
        Relation:=1, _ 
        FormulaText:=1060.9 
        SolverAdd CellRef:=Range("CD" & RowCount), _ 
        Relation:=3, _ 
        FormulaText:=0 
        SolverAdd CellRef:=Range("CD" & RowCount), _ 
        Relation:=1, _ 
        FormulaText:=280 
        SolverAdd CellRef:=Range("CE" & RowCount), _ 
        Relation:=3, _ 
        FormulaText:=0 
        SolverAdd CellRef:=Range("CE" & RowCount), _ 
        Relation:=1, _ 
        FormulaText:=300 
        SolverAdd CellRef:=Range("CF" & RowCount), _ 
        Relation:=3, _ 
        FormulaText:=0 
        SolverAdd CellRef:=Range("CF" & RowCount), _ 
        Relation:=1, _ 
        FormulaText:=300 
        SolverAdd CellRef:=Range("CG" & RowCount), _ 
        Relation:=3, _ 
        FormulaText:=0 
        SolverAdd CellRef:=Range("CG" & RowCount), _ 
        Relation:=1, _ 
        FormulaText:=180 
        SolverAdd CellRef:=Range("CH" & RowCount), _ 
        Relation:=3, _ 
        FormulaText:=0 
        SolverAdd CellRef:=Range("CH" & RowCount), _ 
        Relation:=1, _ 
        FormulaText:=180 
        SolverAdd CellRef:=Range("CI" & RowCount), _ 
        Relation:=3, _ 
        FormulaText:=0 
        SolverAdd CellRef:=Range("CI" & RowCount), _ 
        Relation:=1, _ 
        FormulaText:=22.5 
        SolverAdd CellRef:=Range("CJ" & RowCount), _ 
        Relation:=3, _ 
        FormulaText:=0 
        SolverAdd CellRef:=Range("CJ" & RowCount), _ 
        Relation:=1, _ 
        FormulaText:=22.5 
        SolverAdd CellRef:=Range("CK" & RowCount), _ 
        Relation:=3, _ 
        FormulaText:=0 
        SolverAdd CellRef:=Range("CK" & RowCount), _ 
        Relation:=1, _ 
        FormulaText:=44.7 
        SolverAdd CellRef:=Range("CL" & RowCount), _ 
        Relation:=3, _ 
        FormulaText:=0 
        SolverAdd CellRef:=Range("CL" & RowCount), _ 
        Relation:=1, _ 
        FormulaText:=15 
        SolverAdd CellRef:=Range("CM" & RowCount), _ 
        Relation:=3, _ 
        FormulaText:=0 
        SolverAdd CellRef:=Range("CM" & RowCount), _ 
        Relation:=1, _ 
        FormulaText:=32 
        SolverAdd CellRef:=Range("G" & RowCount), _ 
        Relation:=5 
        SolverAdd CellRef:=Range("K" & RowCount), _ 
        Relation:=5 
        SolverAdd CellRef:=Range("O" & RowCount), _ 
        Relation:=5 
        SolverAdd CellRef:=Range("U" & RowCount), _ 
        Relation:=5 
        SolverAdd CellRef:=Range("Z" & RowCount), _ 
        Relation:=5 
        SolverAdd CellRef:=Range("AE" & RowCount), _ 
        Relation:=5 
        SolverAdd CellRef:=Range("AJ" & RowCount), _ 
        Relation:=5 
        SolverAdd CellRef:=Range("AO" & RowCount), _ 
        Relation:=5 
        SolverAdd CellRef:=Range("AT" & RowCount), _ 
        Relation:=5 
        SolverAdd CellRef:=Range("AY" & RowCount), _ 
        Relation:=5 
        SolverAdd CellRef:=Range("BD" & RowCount), _ 
        Relation:=5 
        SolverAdd CellRef:=Range("BH" & RowCount), _ 
        Relation:=5 
        SolverAdd CellRef:=Range("BM" & RowCount), _ 
        Relation:=5 
        SolverAdd CellRef:=Range("BS" & RowCount), _ 
        Relation:=5 
        SolverAdd CellRef:=Range("BX" & RowCount), _ 
        Relation:=5 
        SolverAdd CellRef:=Range("CN" & RowCount), _ 
        Relation:=2, _ 
        FormulaText:=Range("CO" & RowCount) _ 
         
        SolverSolve UserFinish:=True 
        SolverFinish keepFinal:=1 
        RowCount = RowCount + 1 
    Loop 
    MsgBox "processing over" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you for any consideration of this code,

Steven

Hi, im trying to loop solver using VBA and while the code is quite simple, it doesn't work and iterates without giving me any results

is there anything missing?


	VB:
	
 Macro1() 
     
    Worksheets("bcu2").Activate 
     
     
    Dim x As Integer 
     
     
    Sheets("bcu2").Select 
     
    For x = 5 To 30 
         
        solverok setcell:="$M$(x)", MaxMinval:=3, valueof:="0", bychange:="$N$(x)" 
         
         
        solversolve True 
         
        solverfinish keepfinal:=1 
         
    Next x 
     
     
     
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi

I have a sheet called AUTOCALC which performs end of day calculations,i copied the whole sheet and pasted it into a new sheet called INTRADAY to do,yes you guessed it,INTRADAY calculations.

I have renamed all the sheet references in a new macro from AUTOCALC to INTRADAY and the macro runs ok until it gets to the Solver part.I then get the error message"Solver:An unexpected internal error occurred,or available memory was exhausted".The original macro runs fine and there is plenty of memory available.

Below are the original Solver model settings in AUTOCALC:

1.4455
1.0000
FALSE
100.0000

the formula in the false cell is =$B$87='AUTO CALC'!$F$87

so i thought that maybe once i changed AUTO CALC to INTRADAY it would solve it,
=$B$21=INTRADAY!$F$21

no matter what i do the the apostrophes that surround the word AUTO CALC will not stay in the formula when i change the name to INTRADAY

I don't seem to be able to record a new Solver macro either on the INTRADAY sheet as i get the error message that i referred to earlier
Has anybody got any ideas as to what i am doing wrong?

Below is some of the code that contains the Solver section

Sheets("INTRADAY").Select
Range("T1:U1").Select
ActiveSheet.Paste
Range("AJ1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Sheets("INTRADAY").Select
SolverReset
SolverLoad LoadArea:="$F$14:$F$17"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False
SolverOk SetCell:="$F$21", MaxMinVal:=1, ValueOf:="0", ByChange:="$B$21"
SolverSolve UserFinish:=True
Range("AK1").Select
Sheets("INTRADAY").Select
SolverReset
SolverLoad LoadArea:="$F$46:$F$49"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False
SolverOk SetCell:="$F$54", MaxMinVal:=1, ValueOf:="0", ByChange:="$B$54"
SolverSolve UserFinish:=True
Sheets("INTRADAY").Select
SolverReset
SolverLoad LoadArea:="$F$81:$F$84"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False
SolverOk SetCell:="$F$87", MaxMinVal:=1, ValueOf:="0", ByChange:="$B$87"
SolverSolve UserFinish:=True
Sheets("INTRADAY").Select
SolverReset
SolverLoad LoadArea:="$F$114:$F$117"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False
SolverOk SetCell:="$F$120", MaxMinVal:=1, ValueOf:="0", ByChange:="$B$120"
SolverSolve UserFinish:=True
Sheets("INTRADAY").Select
Range("AJ4:AN44").Select
Selection.ClearContents
Selection.Interior.ColorIndex = xlNone
Range("AW3:AX87").Select
Selection.ClearContents
Selection.Interior.ColorIndex = xlNone
Range("AP29").Select
Range("AA1").Select
ActiveWindow.SmallScroll Down:=-9
Range("AT5:AU86").Select
Selection.Copy
Range("AW5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("AT5:AU86").Select
Selection.Copy

Thanks for your help

Hi. Im having some trouble building a Macro to solve for AC4 = 0 by adjusting I4.

I've built the worksheet, but I just don't know how to write a Macro Solver.

I'm sure that it's a pretty easy macro, but alas, I have no idea how to do it.

AC4 is the target cell, and I want this value to be zero by changing I4

I would then like this to re-iterate from Rows 4 through 79 (I'll make it bigger to fit my spreadsheet of 588 rows when I get it)

I tried to figure it out from reading the VBA tutorial, but Im under a huge deadline, and in the time I have it seemed like trying to go from 0 to 60mph on a pedal bike!

Ive attached the spreadsheet so hopefully some nice soul will write the VBA code and send it back for me to insert into the worksheet!

PLEASE PLEASE PLEASE J

Or should I be saying THANK YOU IN ADVANCE!

Trying to get a macro to run 3500+ iterations of solver based on cell reference constraints. Code is below, and cant figure out how to properly reference cells so constraints are input into solver correctly.


	VB:
	
 '
Dim x As Integer 
 
For x = 3 To 10 
     
    SolverReset 
     
    SolverOk SetCell:=Cells(x, 10), MaxMinVal:=3, ValueOf:=1, ByChange:=Range("F" & x), Range("I" & x)), _ 
    Engine:=1, EngineDesc:="GRG Nonlinear" 
    SolverAdd CellRef:=Cells(x, 11), Relation:=2, formulatext:=Cells(x, 5) 
    SolverAdd CellRef:=Cells(x, 12), Relation:=2, formulatext:=Cells(x, 2) 
    SolverAdd CellRef:=Cells(x, 13), Relation:=2, formulatext:=Cells(x, 3) 
    SolverSolve userFinish:=True 
    SolverDelete CellRef:=Cells(x, 11), Relation:=2, formulatext:="Cells(x, 5)" 
    SolverDelete CellRef:=Cells(x, 12), Relation:=2, formulatext:="Cells(x, 2)" 
    SolverDelete CellRef:=Cells(x, 13), Relation:=2, formulatext:="Cells(x, 3)" 
    SolverFinish keepFinal:=1 
     
     
     
Next 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Any help would be appreciated!

I am using solver to get a list of values that make up mulitiple known values. I would like to make the code easier to apply instead of copying it and changing it for every cell. Auto Merged Post Until 24 Hrs Passes;


	VB:
	
 
Sub Macro() 
     '
     ' Macro Macro
     ' Macro recorded 4/29/2008 by Jessica L Olson
     
    SolverReset 
    SolverOk SetCell:="$F$28", MaxMinVal:=3, ValueOf:="0.002", ByChange:= _ 
    "$F$31:$F$37" 
    SolverAdd CellRef:="$F$31:$F$37", Relation:=1, FormulaText:="100%" 
    SolverAdd CellRef:="$F$31:$F$37", Relation:=3, FormulaText:="0" 
    SolverAdd CellRef:="$F$31:$F$37", Relation:=3, FormulaText:="$E$31:$E$37" 
    SolverSolve userFinish:=True 
     
    SolverReset 
    SolverOk SetCell:="$G$28", MaxMinVal:=3, ValueOf:="0.008", ByChange:= _ 
    "$G$31:$G$37" 
    SolverAdd CellRef:="$G$31:$G$37", Relation:=1, FormulaText:="100%" 
    SolverAdd CellRef:="$G$31:$G$37", Relation:=3, FormulaText:="0" 
    SolverAdd CellRef:="$G$31:$G$37", Relation:=3, FormulaText:="$F$31:$F$37" 
    SolverSolve userFinish:=True 
     
    SolverReset 
    SolverOk SetCell:="$H$28", MaxMinVal:=3, ValueOf:="0.031", ByChange:= _ 
    "$H$31:$H$37" 
    SolverAdd CellRef:="$H$31:$H$37", Relation:=1, FormulaText:="100%" 
    SolverAdd CellRef:="$H$31:$H$37", Relation:=3, FormulaText:="0" 
    SolverAdd CellRef:="$H$31:$H$37", Relation:=3, FormulaText:="$G$31:$G$37" 
    SolverSolve userFinish:=True 
     
    SolverReset 
    SolverOk SetCell:="$I$28", MaxMinVal:=3, ValueOf:="0.088", ByChange:= _ 
    "$I$31:$I$37" 
    SolverAdd CellRef:="$I$31:$I$37", Relation:=1, FormulaText:="100%" 
    SolverAdd CellRef:="$I$31:$I$37", Relation:=3, FormulaText:="0" 
    SolverAdd CellRef:="$I$31:$I$37", Relation:=3, FormulaText:="$H$31:$H$37" 
    SolverSolve userFinish:=True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I'm running the solver using VBA and the solver stops after 20 minutes, giving me the message: "The maximum time limit was reached; continue anyway?". I can then choose between Continue/Stop/Save scenario../Help. Is there any way to prevent the solver from stopping after 20 minutes?

Hi all, I'm working with Solver Add-in and I wanted to run it with a macro. But I faced a problem. I wrote the code automatically with the Tools, and the code is described above:

Sub Macro4()
SolverLoad LoadArea:="CEN3M"
SolverOptions MaxTime:=1000, Iterations:=1000, Precision:=0.01, AssumeLinear:= _
True, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=10, Scaling:=True, Convergence:=0.0001, AssumeNonNeg:=True
SolverOk SetCell:="$CC$21", MaxMinVal:=2, ValueOf:="0", ByChange:= _
"$BU$8:$CV$12,$BV$13:$BX$13,$BX$14,$BZ$13:$CD$13,$CC$14:$CD$14,$CF$13:$CG$13,$CG$14,$CI$13:$CJ$13,$CJ$14,$CM$13:$CP$13,$CN$14:$CP$14,$CR$13:$CS$13,$CS$14,$CU$13:$CV$13,$CV$14,$CW$8:$DC$11"
SolverSolve
End Sub

When I run the macro in my worksheet, Excel returns me this message: "Sub or Funtion not defined". What's wrong with my code?

Note that the Load Area "CEN3M" has all the constraints, and options.

Hi all,

I am new to this forum and am fairly new with vba.

I am writing a macros where I need to call Solver to optimise a solution and iterate it 50 times. I would like to disable screen updates to prevent the screen from flashing. Normally I would use:


	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
In this case it does not seem to work. Alternatively, if there is a better solution available, please let me know.

Thanks.

Hi guys,

I've done quite a bit of research in the past week or so on the topic of solver automation using loops; while there are a few good posts scattered around, i haven't quite found the solution to my problem.

i'm trying to run the solver on a few like-sized datasets contained in one worksheet. i've set up a loop to properly reference the 4 datasets. the solver constraints are in one column on the same worksheet.

the macro runs with no errors, but it doesn't "solve." the parameter cells retain their original values.

i should mention that my macro ran perfectly before i consolidated the 4 separate solver calls into one loop. I got tired of having to type 4 different constraint changes in the code as i was optimizing the solver.

on a similar topic, does anyone know how to call a variable in the FormulaText declaration? If i wanted to step through a defined array of constraints, say [1,2,3], how do i get FormulaText:= variable to work?


	VB:
	
 
Sub Macrosolver() 
     
    Dim iteration As Long 
    Dim i As Integer 
    i = 8 'start in row 8 at first dataset
     
    For iteration = 1 To 4 
        With ActiveWorkbook.Sheets("Solver") 
            SolverReset 
            SolverOk SetCell:="$G$" & i, MaxMinVal:=3, ValueOf:="$L$3", ByChange:="$C$" & i & ":$F$" & i 
            SolverAdd CellRef:="$C$" & i, Relation:=3, FormulaText:="$L$5" 
            SolverAdd CellRef:="$C$" & i, Relation:=1, FormulaText:="$L$4" 
            SolverAdd CellRef:="$D$" & i, Relation:=3, FormulaText:="$L$7" 
            SolverAdd CellRef:="$D$" & i, Relation:=1, FormulaText:="$L$6" 
            SolverAdd CellRef:="$E$" & i, Relation:=3, FormulaText:="$L$9" 
            SolverAdd CellRef:="$E$" & i, Relation:=1, FormulaText:="$L$8" 
            SolverAdd CellRef:="$F$" & i, Relation:=3, FormulaText:="$L$11" 
            SolverAdd CellRef:="$F$" & i, Relation:=1, FormulaText:="$L$10" 
            SolverAdd CellRef:="$H$" & i, Relation:=2, FormulaText:="1" 
            i = i - 2 'move up two rows
            SolverAdd CellRef:="$G$" & i, Relation:=3, FormulaText:="$L$13" 
            SolverAdd CellRef:="$G$" & i, Relation:=1, FormulaText:="$L$12" 
            i = i + 1 'move down one row
            SolverAdd CellRef:="$G$" & i, Relation:=3, FormulaText:="$L$15" 
            SolverAdd CellRef:="$G$" & i, Relation:=1, FormulaText:="$L$14" 
            SolverSolve userFinish:=True 
            SolverFinish KeepFinal:=1 
            i = i + 8 'move down to next dataset
        End With 
    Next iteration 
     
    Sheets("Main Sheet").Activate 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks, and happy new year to all!

Hi,

Problem : Solver keeps giving me this message- Do you want to replace the contents of the destination cells?. And therefore, my routine is not fully automatic. Note that I have set Application.DisplayAlerts = False.

What confuses me more is that I recycled this code from an old project. The only things I changed were the cell addresses. And, the old project still works fine ( i.e doesnt give me the message). Can you please suggest a remedy? Thanks.

Please see the code below.


	VB:
	
 CommandButton1_Click() 
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Dim i As Integer 
     
    For i = 25 To 31 ' rows in Return Table
         'Copy the initial portfolio weights and target return into the calculation area
        Range(Cells(i, 4), Cells(i, 13)).Select ' columns in Return Table
        Selection.Copy 
        Range("D66:N66").Select 
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
         
         'Copy target return to the calcuation area
         
        Cells(i, 1).Select 
        Selection.Copy 
        Range("B78").Select 
         
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
         
        SolverOk SetCell:="$B$79", MaxMinVal:=2, ByChange:="$D$66:$N$66" 
         ' minimize variance
        SolverDelete CellRef:="$C$66", Relation:=2, FormulaText:="1" 
        SolverAdd CellRef:="$C$66", Relation:=2, FormulaText:="1" 'keep weights in 100% range
        SolverDelete CellRef:="$C$65", Relation:=2, FormulaText:="$B$78" 
        SolverAdd CellRef:="$C$65", Relation:=2, FormulaText:="$B$78" 
         
         
        SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, StepThru:=False, AssumeNonNeg:=True 
        SolverSolve userFinish:=True ' [B][COLOR="Red"]THE MESSAGE APPEARS HERE[/COLOR][/B]
        SolverFinish KeepFinal:=1 
        SolverReset 
         
        Range("D66:N66").Select 
        Selection.Copy 
        Range(Cells(i, 4), Cells(i, 13)).Select 
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
         
        Range("C65").Select 
        Selection.Copy 
        Cells(i, 1).Select 
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
         
        Range("B80").Select 
        Selection.Copy 
        Cells(i, 2).Select 
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
         
    Next i 
     
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have to use solver for hundreds of columns and I tried to automate this by a macro. I just want to change A31 into B31, A32, into B32, etc.

The macro is running, doesn't give errors, but isn't working.
Apparently I am doing something wrong. Can anyone please help me?

My macro:


	VB:
	
 Niels() 
     '
     ' Niels Macro
     ' De macro is opgenomen op 2-10-2006 door Maarssenbroek.
     '
     ' Sneltoets: CTRL+SHIFT+N
     '
    SolverReset 
    SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _ 
    :=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _ 
    IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True 
     
    For counter = 2 To 101 
        Set curcell31 = ActiveSheet.Cells(31, counter) 
        Set curcell32 = ActiveSheet.Cells(32, counter) 
        Set curcell33 = ActiveSheet.Cells(33, counter) 
        Set curcell34 = ActiveSheet.Cells(34, counter) 
        Set curcell35 = ActiveSheet.Cells(35, counter) 
        Set curcell36 = ActiveSheet.Cells(36, counter) 
        Set curcell37 = ActiveSheet.Cells(37, counter) 
         
        SolverOk SetCell:="curcell31", MaxMinVal:=3, ValueOf:="0", ByChange:= _ 
        "ActiveSheet.Range(.Cells(32, counter),.cells(33, counter),.cells(34, counter),.cells(36, counter))" 
        SolverAdd CellRef:="curcell33", Relation:=3, FormulaText:="curcell32" 
        SolverAdd CellRef:="curcell34", Relation:=3, FormulaText:="curcell32" 
        SolverAdd CellRef:="curcell36", Relation:=3, FormulaText:="curcell33" 
        SolverAdd CellRef:="curcell36", Relation:=3, FormulaText:="curcell34" 
        SolverAdd CellRef:="curcell36", Relation:=3, FormulaText:="curcell37" 
        SolverAdd CellRef:="curcell37", Relation:=3, FormulaText:="curcell35" 
        SolverSolve 
    Next counter 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi there... Im new to this.. but Ive been bothered by this problem for so long that Im coming here for help.

Im implementing an optimization algorithm using Excel Solver in the code.

The algorithm constructs the cells with the Objective Function, the Restrictions, and the Variables. In each iteration it constructs a different Linear Program, but allways in the same sheet. Part of the code re-defines the ranges to be given to Solver as Restrictions. I added a procedure that loads the reference to Solver.xla, which I quote here:


	VB:
	
 InstallSolver() 
    Dim wb As Workbook 
     
    Sheets("LP").Select 
     
    On Error Resume Next 
     ' Set a Reference to the workbook that will hold Solver
    Set wb = ActiveWorkbook 
     
    With wb.VBProject.References 
        .Remove .Item("SOLVER") 
    End With 
     
    With AddIns("Solver Add-In") 
        .Installed = False 
        .Installed = True 
        wb.VBProject.References.AddFromFile .FullName 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So... after this is done... the code calls solver in this way:


	VB:
	
 PLRobusto() 
    Worksheets("LP").Activate 
    SOLVER.SolvReset 
    SOLVER.SolvOk SetCell:="ey", MaxMinVal:=2, ByChange:= _ 
    "vector_w, t, vector_y_a_" & CStr(iteracion) & ",vector_y_b_" & CStr(iteracion) 
    SOLVER.SolvAdd CellRef:="vector_wXa_t_" & CStr(iteracion), Relation:=3, FormulaText:="vector_m_y_a_" & CStr(iteracion) 
    SOLVER.SolvAdd CellRef:="vector_wXb_t_" & CStr(iteracion), Relation:=3, FormulaText:="vector_m_y_b_" & CStr(iteracion) 
    SOLVER.SolvAdd CellRef:="vector_y_a_" & CStr(iteracion), Relation:=3, FormulaText:="0" 
    SOLVER.SolvAdd CellRef:="vector_y_b_" & CStr(iteracion), Relation:=3, FormulaText:="0" 
    SOLVER.SolvOptions MaxTime:=100, Iterations:=100, AssumeLinear _ 
    :=True 
    SOLVER.SolvSolve userFinish:=True 
    SOLVER.SolvFinish KeepFinal:=1 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The ranges written between quotation marks refere to the ranges named by that string (which is done before in the main code).

In my PC, this runs perfectly. However, Ive to work in my office, from where Im writting right now... which has the same version of Excel, Windows 2000, etc... but it gets stuck in the first line that uses Solver.Solv*, giving a 424 error.

How can I resolve this? Im desperate to finish this algorithm...

Ozono


No luck finding an answer? You could always try Google.