Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting solver constraints in VB

    I wrote the following code to automate Solver:

    SolverOk SetCell:=ActiveCell.Offset(0, 0), MaxMinVal:=3, ValueOf:="0.95", ByChange:=ActiveCell.Offset(0, -1)
    SolverAdd CellRef:=ActiveCell.Offset(0, -1), Relation:=1, FormulaText:=ActiveCell.Offset(-1, -1)
    SolverAdd CellRef:=ActiveCell.Offset(0, -1), Relation:=3, FormulaText:=ActiveCell.Offset(0, -3)
    SolverSolve
    SolverDelete CellRef:=ActiveCell.Offset(0, -1), Relation:=1, FormulaText:=ActiveCell.Offset(-1, -1)
    SolverDelete CellRef:=ActiveCell.Offset(0, -1), Relation:=3, FormulaText:=ActiveCell.Offset(0, -3)

    When run, it correctly adds two constraints (but it replaces cell references with their values on the righthand side of the constraints -- e.g., I expected "$B$4<=$B$3" but got "$B$4<=0.2125" -- but the solver still works).

    My problem is the SolverDelete lines - they don't seem to do anything, and they certainly don't delete the constraints. After running the macro many times, I have to delete a lot of constraints by hand. Anyone run across this problem? Thanks a bunch.

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting solver constraints in VB

    Can I suggest the following modification:
    SolverDelete CellRef:=ActiveCell.Offset(0, -1).addresslocal, Relation:=1, FormulaText:=ActiveCell.Offset(-1, -1)
    SolverDelete CellRef:=ActiveCell.Offset(0, -1).addresslocal, Relation:=3, FormulaText:=ActiveCell.Offset(0, -3)

    This worked for me under XL97.

    If you use addresslocal in your solveradd calls, you'll get the address in your constraints as you expected rather than the values.

    Jon

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting solver constraints in VB

    Thanks, Jon. Your suggestion worked great.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •