Getting relative reference macro to run with solver in it.

[jlvoigt1]

I have some experience with solver and macros, but not a lot. I am trying to run a macro with relative references. It is optimizing 8 variables in a row with up and lower limits, the sum of which must add to 1. It is a mix optimization macro. The solver solution is simple, but I need to have it run on a specific row in a spreadsheet. Ultimately I want to add to it to optimize all 140 rows in one process, but I am just trying to get one row to work first. It is frustrating, because it all appears to work, but the solver solution does not run.

It was built based on recording the macro. I'm hoping some people with bigger brains than mine can spot the problem easily. Any and all help is greatly appreciated.

Sub Macro2()
'
' Macro2 Macro
' Market Opt
'
' Keyboard Shortcut: Ctrl+o
'
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlToLeft).Select
SolverOk SetCell:="$AR$11", MaxMinVal:=1, ValueOf:="0", ByChange:= _
"$AD$11:$AK$11"
SolverAdd CellRef:="$AD$11", Relation:=1, FormulaText:="$AD$5"
SolverAdd CellRef:="$AE$11", Relation:=1, FormulaText:="$AE$5"
SolverAdd CellRef:="$AF$11", Relation:=1, FormulaText:="$AF$5"
SolverAdd CellRef:="$AG$11", Relation:=1, FormulaText:="$AG$5"
SolverAdd CellRef:="$AH$11", Relation:=1, FormulaText:="$AH$5"
SolverAdd CellRef:="$AI$11", Relation:=1, FormulaText:="$AI$5"
SolverAdd CellRef:="$AJ$11", Relation:=1, FormulaText:="$AJ$5"
SolverAdd CellRef:="$AK$11", Relation:=1, FormulaText:="$AK$5"
SolverAdd CellRef:="$AD$11:$AK$11", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$AS$11", Relation:=2, FormulaText:="1"
SolverOk SetCell:="$AR$11", MaxMinVal:=1, ValueOf:="0", ByChange:= _
"$AD$11:$AK$11"
SolverSolve UserFinish:=True
Application.Run "SolverSolve", True
ActiveWindow.SmallScroll ToRight:=1
SolverReset
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
Selection.End(xlToRight).Select
ActiveCell.Offset(1, 1).Range("A1").Select
End Sub

[jlvoigt1]

A quick clarification, the solver app does run, and I get the OK prompt, but it is not meeting the selection criteria. It ran correctly when I recorded it, but not within the macro.

[xladept]

Hi,

Get rid of those scrolls and your macro will probably work!

[jlvoigt1]

Thanks for the scroll hint. I deleted and reran, but it did not fix my particular problem. The part of the solver solution that is not working is the sum of the solution, which is in cell AS11 in this execution must equal 1, and it is not doing that, the problem might be in or around this line

SolverAdd CellRef:="$AS$11", Relation:=2, FormulaText:="1"

[jlvoigt1]

No, the solver is not running at all. It just gives me back the initial starting point.

[jeffreybrown]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Please PM me when you have updated the thread title and this post will be removed.