PDA

View Full Version : [SOLVED] Solver Question



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

Jacob Hilderbrand
01-19-2005, 08:57 PM
The first argument for the Solver is the cell that is changing (It needs to be a Range and not a Value). Try this.


SolverOk SetCell:=c.Offset(0, 5), MaxMinVal:=3, ValueOf:=a, _
ByChange:=c.Offset(-5, 3)

Zack Barresse
01-20-2005, 12:42 PM
The first argument for the Solver is the cell that is changing (It needs to be a Range and not a Value). Try this.


SolverOk SetCell:=c.Offset(0, 5), MaxMinVal:=3, ValueOf:=a, _
ByChange:=c.Offset(-5, 3)

It also needs to be a formula.

stapuff
01-20-2005, 03:16 PM
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

Jacob Hilderbrand
01-20-2005, 06:34 PM
It also needs to be a formula. :what: Not sure what you mean Zack.

Jacob Hilderbrand
01-20-2005, 06:39 PM
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.
If you want to check for several conditions you can build a Select Case statement.



Select Case c.Text
Case Is = "Weak"
'do something
Case Is = "Weak" & c.Offset(0, 2).Text
'do something else
Case Else
'do a third thing here
End Select

stapuff
01-20-2005, 09:36 PM
DRJ -

Thanks for your reply back. I have only used a Select Case once before. I will need to spend a little time doing some looking up on the subject. Thanks for the suggestion!

Kurt

Jacob Hilderbrand
01-20-2005, 09:53 PM
You're Welcome

Take Care

Zack Barresse
01-20-2005, 11:02 PM
The cell you Set in Solver, the first syntax, it should be a formula, not just a cell value. I *think* you can get away with values, but it's usually suggested to keep it as a formula, afaik. (Which isn't a whole lot!)

Jacob Hilderbrand
01-20-2005, 11:24 PM
c.Offset(0, 5) This is a range so it should work fine. A value would not work since we need the cell to work with.