Copying Solver results of each iteration to a given location in a workbook
 

I am quite new to programming and would be glad if someone can help with perfecting part of the code of the macro I have written. The macro calls a solver and the solver iterates several times producing new answers at each iteration. I would like the answers, ie., change cell from each iteration to be copied (transposed) and target cell values(copy values only) to a given location in the workbook. Thus I would like cells E26:27 to be copied to C35:D35 and cell E29 to E35. the result of each iteration should be copied to a row below the other creating a table of values from each iteration. The part that is critical is the copying of the result and will be glad for any assistance. I have included the code I am using below and attached the Excel sheet.

Sub Macro4SOLVEMACRO()
'
' Macro4SOLVEMACRO Macro
'
' Keyboard Shortcut: Ctrl+Shift+X
'

For i = 1 To 10

SolverOk SetCell:="$E$29", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$26:$E$27", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$E$29", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$26:$E$27", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve UserFinish:=True
Dim r As Long


Range("E26:E27").Select
Selection.Copy
Range("C35").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E29").Select
Application.CutCopyMode = False
Selection.Copy
Range("E35").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next
End Sub

Copying Solver results of each iteration to a given location in a workbook
 

Romario,

There is a great Add-In available for free through Indiana University called SolverTable. It will create a one- or two-way data table of Solver results. You can download it at:

http://www.kelley.iu.edu/albrightboo..._downloads.htm

If you would prefer to stick with the macro, then replace everything below "Dim r as Long" with this:

With Range("C34")
.Offset(i, 0).Value = Range("E26").Value
.Offset(i, 1).Value = Range("E27").Value
.Offset(i, 2).Value = Range("E35").Value
End With
End Sub

What this will do is use the value of "i" (in your code "i" is a number between 1 and 10) and will offset from cell C34 "i" rows. For example, you want the first values to go to cells C35:E35. In that case, "i" = 1, so Range("C34").Offset(i, 0) would be cell C35, while Range("C34").Offset(i, 1) would be cell D35 etc.

Good Luck,

Ben

Very many thanks Ben.

The contributed code worked well. About the SolverTable, I downloaded it and uploaded the add-on in Excel. I am still trying to understand its functioning and possible utility in my case. Thank you.