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

[Romario20march]

I am quite new to programming and would be glad if someone can help wit
perfecting part of the code of the macro I have written. The macro call
a solver and the solver iterates several times producing new answers a
each iteration. I would like the answers, ie., change cell from eac
iteration to be copied (transposed) and target cell values(copy value
only) to a given location in the workbook. Thus I would like cell
E26:27 to be copied to C3535 and cell E29 to E35. the result of eac
iteration should be copied to a row below the other creating a table o
values from each iteration. The part that is critical is the copying o
the result and will be glad for any assistance. I have included the cod
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 Su

 

[Ben McClave]

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/albrightbooks/Free_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