Hi everyone,

I have this simple 3 subs vba code. It runs 8 times and each solution
has 24 values that is put in one column of 24 raws. So, the 1st
solution found gets written at N2 to N25....and so on.

The problem I have is this: The 1st solution that gets written from
N2-N25, later it gets removed; just the 1st solution! What could be
causing this in the code? Thanks in advance.

Option Explicit
Option Base 1
Sub Pareto()
Dim cell As Range, ModelCounter As Integer, JobNr As Integer
Dim ret As Integer
Dim ARngSolution As Range

Application.ScreenUpdating = False

ModelCounter = 0
Set ARngSolution = Range("SolDVSens")
ARngSolution.ClearContents

For JobNr = 1 To 8

ret = SolverSolve(UserFinish:=True)

If ret = 0 Or ret = 14 Then
ModelCounter = ModelCounter + 1
RunSolver
StoreResults ModelCounter
End If
Next
End Sub


Sub RunSolver()
SolverReset
SolverOk SetCell:=Range("Obj2"), MaxMinVal:=1,
ByChange:=Range("Picked")
SolverAdd CellRef:=Range("Spending"), Relation:=1,
FormulaText:="Budget"
SolverAdd CellRef:=Range("Picked"), Relation:=5
SolverOptions AssumeLinear:=True, AssumeNonNeg:=True
End Sub

Sub StoreResults(ModelCounter As Integer)
Dim i As Integer
With Range("N1")
For i = 1 To 24
.Offset(i, ModelCounter) = Range("Picked").Cells(i)
Next
End With
End Sub

Mike:

The first thing I'd do is set Break Points in the code at various places to
catch what is going on. If you click on the left margin of your code, a red
dot appears (or press F9). This allows you to run the macro with the code
window open using F5, and the code will pause at these points to let you
check the state of variables and the condition of your results. Pressing F5
gain will run the code until the next Break Point.

If I were debugging this, I'd set Break Points at
here If ret = 0 Or ret = 14 Then
ModelCounter = ModelCounter + 1
here RunSolver
here StoreResults ModelCounter
End If

This way, you can check the values of ret and ModelCounter as well as see
what your worksheet looks like after your other Subs run. See if you can
identify at what point the cell goes blank.

Also, you have
Dim cell As Range
Don't use "cell" as it is a valid object name. Use something like "cll" or
"objCell" so you know what it is but Excel doesn't get confused.

Lastly, either post to only one forum, or put both newsgroups in the TO
header. If you put in both NG, any answer will show up on both NG, and
everyone is on the same page. Else you may wind up chasing one thought here
and another there.

HTH
Ed

"Mike" wrote in message
oups.com...
Hi everyone,

I have this simple 3 subs vba code. It runs 8 times and each solution
has 24 values that is put in one column of 24 raws. So, the 1st
solution found gets written at N2 to N25....and so on.

The problem I have is this: The 1st solution that gets written from
N2-N25, later it gets removed; just the 1st solution! What could be
causing this in the code? Thanks in advance.

Option Explicit
Option Base 1
Sub Pareto()
Dim cell As Range, ModelCounter As Integer, JobNr As Integer
Dim ret As Integer
Dim ARngSolution As Range

Application.ScreenUpdating = False

ModelCounter = 0
Set ARngSolution = Range("SolDVSens")
ARngSolution.ClearContents

For JobNr = 1 To 8

ret = SolverSolve(UserFinish:=True)

If ret = 0 Or ret = 14 Then
ModelCounter = ModelCounter + 1
RunSolver
StoreResults ModelCounter
End If
Next
End Sub


Sub RunSolver()
SolverReset
SolverOk SetCell:=Range("Obj2"), MaxMinVal:=1,
ByChange:=Range("Picked")
SolverAdd CellRef:=Range("Spending"), Relation:=1,
FormulaText:="Budget"
SolverAdd CellRef:=Range("Picked"), Relation:=5
SolverOptions AssumeLinear:=True, AssumeNonNeg:=True
End Sub

Sub StoreResults(ModelCounter As Integer)
Dim i As Integer
With Range("N1")
For i = 1 To 24
.Offset(i, ModelCounter) = Range("Picked").Cells(i)
Next
End With
End Sub