Solver Question

[stapuff]

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]

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]

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]

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]

It also needs to be a formula.

Not sure what you mean Zack.

[Jacob Hilderbrand]

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]

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]

You're Welcome

Take Care

[Zack Barresse]

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]

[VBA]c.Offset(0, 5)[/VBA] This is a range so it should work fine. A value would not work since we need the cell to work with.