Alright - a update.
The following Solver code works perfect. I would like to add 2 things to it.
1. I would like to clearcontents & set the rng with the same loop. On my worksheet the loop :
ActiveSheet.Range("AV" & i + 1 & ":AV300").ClearContents
takes me to the correct row.
2. I would like to build more into this:
If c.Value = "Week" Then to something like If c.value = "Week" & c.offset(0,2).Value = 0 then goto Next c.
Thanks,
Kurt
Sub optprodn()
Dim c As Variant
Dim rng As Range
Dim i, l As Long
Sheets("sheet1").Select
i = Range("AS3").Row
While Cells(i, 45) <> "Week"
i = i + 1
Wend
ActiveSheet.Range("AV" & i + 1 & ":AV300").ClearContents
'Set rng = ActiveSheet.Range("AS" & i + 1).End(xlDown)
Sheets("sheet1").Select
Set rng = Range([AS9], [AS9].End(xlDown))
For Each c In rng
If c.Value = "Week" Then
a = c.Offset(0, 2).Value
SolverReset
SolverOk SetCell:=c.Offset(0, 5), MaxMinVal:=3, ValueOf:=a, _
ByChange:=c.Offset(-4, 3).Address & ":" & c.Offset(0, 3).Address
SolverAdd CellRef:=c.Offset(-4, 3).Address & _
":" & c.Offset(0, 3).Address, Relation:=1, FormulaText:="3"
SolverAdd CellRef:=c.Offset(-4, 3).Address & _
":" & c.Offset(0, 3).Address, Relation:=3, FormulaText:="1"
SolverAdd CellRef:=c.Offset(-4, 3).Address & _
":" & c.Offset(0, 3).Address, Relation:=4, FormulaText:="integer"
SolverSolve UserFinish:=True
End If
Next c
End Sub