stapuff
01-19-2005, 08:34 PM
When using solver - does the cell ranges have to be static (i.e. $D$12) or can they be variable? I created a macro I hope would work but I am getting a error when I attempt to run it.
Compile error - "Argument Not Optional" What is the cause and solution if any?
Errors on: SolverAdd
I have never had the code run though and actually work, however, this error occurred after I put .value in the macro. If I take .value out the code runs through but does nothing.
Any help would be greatfully appreciated
Thanks,
Kurt
Sub optprodn()
Dim c As Variant
Dim rng As Range
'On Error Resume Next
Sheets("sheet1").Range("AV3:AV325").ClearContents
Sheets("sheet1").Select
Set rng = Range([AS3], [AS3].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).Value, MaxMinVal:=3, ValueOf:=a, _
ByChange:=c.Offset(-5, 3).Value: c.Offset(0, 3).Value
SolverAdd CellRef:=c.Offset(-5, 3).Value: c.Offset(0, 3).Value , _
Relation:=1, FormulaText:="3"
SolverAdd CellRef:=c.Offset(-5, 3).Value: c.Offset(0, 3).Value , _
Relation:=3, FormulaText:="1"
SolverAdd CellRef:=c.Offset(-5, 3).Value: c.Offset(0, 3).Value , _
Relation:=4, FormulaText:="integer"
SolverSolve
End If
Next c
End Sub
Compile error - "Argument Not Optional" What is the cause and solution if any?
Errors on: SolverAdd
I have never had the code run though and actually work, however, this error occurred after I put .value in the macro. If I take .value out the code runs through but does nothing.
Any help would be greatfully appreciated
Thanks,
Kurt
Sub optprodn()
Dim c As Variant
Dim rng As Range
'On Error Resume Next
Sheets("sheet1").Range("AV3:AV325").ClearContents
Sheets("sheet1").Select
Set rng = Range([AS3], [AS3].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).Value, MaxMinVal:=3, ValueOf:=a, _
ByChange:=c.Offset(-5, 3).Value: c.Offset(0, 3).Value
SolverAdd CellRef:=c.Offset(-5, 3).Value: c.Offset(0, 3).Value , _
Relation:=1, FormulaText:="3"
SolverAdd CellRef:=c.Offset(-5, 3).Value: c.Offset(0, 3).Value , _
Relation:=3, FormulaText:="1"
SolverAdd CellRef:=c.Offset(-5, 3).Value: c.Offset(0, 3).Value , _
Relation:=4, FormulaText:="integer"
SolverSolve
End If
Next c
End Sub