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

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

- Call Function At Each Iteration Of Solver
- Solver, iterations, excel, VBA, solversolve, solverfinish
- VBA loop calling Solver broken in Excel 2011
- Call macro after each iteration of solver
- Solver VBA call Sub each iteration
- Using Solver in a loop: Solver doesn't finish before next iteration (Excel 2011)
- Return Number Of Iterations & Solution From Solver Macro Code
- Solver and iterations
- Animated gif while solver is iterating
- Using Solver With Another Iterative Routine
- Catching max iterations Stop of Solver in VBA
- VBA Code for Solver Loop does not change cells
- Looping solver not working
- Solver Problems
- Desperate for Solver Macro help!
- Solver Macro using row(cell reference) loop not working
- Automate Multiple Iterations Of Solver
- Prevent Solver Time Limit (Iterations)
- VBA : Run Solver with a Macro
- Turn off screen application update for Solver
- Solver Automation VBA Code
- Disable Solver Message Asking To Replace Cell Contents
- Macro To Automate Solver For All Columns
- Call to Excel SOLVER gives 424 in a VBA Macro

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: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.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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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".

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 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?

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

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 Module1If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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.

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:Thanks a lot! Best Regardsteste2() 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 FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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?

I´m 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 isn´t 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 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?

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?

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:Thank you for any consideration of this code,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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Steven

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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 I’m under a huge deadline, and in the time I have it seemed like trying to go from 0 to 60mph on a pedal bike!

I’ve 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!

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Any help would be appreciated!

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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.

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

Thanks.

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:Thanks, and happy new year to all!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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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 doesn’t 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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I´m 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:So... after this is done... the code calls solver in this way: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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:The ranges written between quotation marks refere to the ranges named by that string (which is done before in the main code).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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

In my PC, this runs perfectly. However, I´ve to work in my office, from where I´m 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? I´m desperate to finish this algorithm...

Ozono

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