Notices
Results 1 to 2 of 2

Thread: Using Solver in an Excel macro

  1. #1 Using Solver in an Excel macro 
    Forum Isotope Bunbury's Avatar
    Join Date
    Sep 2007
    Location
    Colorado
    Posts
    2,590
    I'm a stranger here - I think this is my very first post in this section. I'm hoping someone can help me with this since I'm not all that familiar with VBA programming and am definitely not a programmer.

    I have a VBA macro in a spreadsheet that uses Solver to converge a number of variables simultaneously. However each Solver solution represents a 1 minute increment in a spreadsheet that has to model several hours of such increments. I need to write a loop that will converge solver for each increment successively. In the sample below the first increment is located in column E and the second in column F and so on. I need to go to column CZ or thereabouts and don't want to have to copy over the solver code that many times and then manually change the column reference in each one. Can I write a loop that will change the column reference and re-run solver the necessary number of times?

    Thanks in advance. Any advice will be gratefully received.

    SolverOk SetCell:="$E$25", MaxMinVal:=3, ValueOf:="0", ByChange:= _
    "$E$10,$E$14,$E$28,$E$29,$E$41"
    SolverAdd CellRef:="$E$37", Relation:=2, FormulaText:="0"
    SolverOk SetCell:="$E$25", MaxMinVal:=3, ValueOf:="0", ByChange:= _
    "$E$10,$E$14,$E$28,$E$29,$E$41"
    SolverAdd CellRef:="$E$38", Relation:=2, FormulaText:="0"
    SolverOk SetCell:="$E$25", MaxMinVal:=3, ValueOf:="0", ByChange:= _
    "$E$10,$E$14,$E$28,$E$29,$E$41"
    SolverAdd CellRef:="$E$47", Relation:=2, FormulaText:="0"
    SolverOk SetCell:="$E$48", MaxMinVal:=3, ValueOf:="0", ByChange:= _
    "$E$10,$E$14,$E$28,$E$29,$E$41"
    SolverAdd CellRef:="$E$48", Relation:=2, FormulaText:="0"
    SolverOk SetCell:="$E$48", MaxMinVal:=3, ValueOf:="0", ByChange:= _
    "$E$10,$E$14,$E$28,$E$29,$E$41"
    SolverSolve

    SolverOk SetCell:="$f$25", MaxMinVal:=3, ValueOf:="0", ByChange:= _
    "$f$10,$f$14,$f$28,$f$29,$f$41"
    SolverAdd CellRef:="$f$37", Relation:=2, FormulaText:="0"
    SolverOk SetCell:="$f$25", MaxMinVal:=3, ValueOf:="0", ByChange:= _
    "$f$10,$f$14,$f$28,$f$29,$f$41"
    SolverAdd CellRef:="$f$38", Relation:=2, FormulaText:="0"
    SolverOk SetCell:="$f$25", MaxMinVal:=3, ValueOf:="0", ByChange:= _
    "$f$10,$f$14,$f$28,$f$29,$f$41"
    SolverAdd CellRef:="$f$47", Relation:=2, FormulaText:="0"
    SolverOk SetCell:="$f$48", MaxMinVal:=3, ValueOf:="0", ByChange:= _
    "$f$10,$f$14,$f$28,$f$29,$f$41"
    SolverAdd CellRef:="$f$48", Relation:=2, FormulaText:="0"
    SolverOk SetCell:="$f$48", MaxMinVal:=3, ValueOf:="0", ByChange:= _
    "$f$10,$f$14,$f$28,$f$29,$f$41"
    SolverSolve


    Reply With Quote  
     

  2.  
     

  3. #2  
    Forum Isotope Bunbury's Avatar
    Join Date
    Sep 2007
    Location
    Colorado
    Posts
    2,590
    I know you guys don't care, :P but anyway I figured this out and here's the code:


    For Counter = 0 To 90 Step 1

    Column = 5 + Counter
    SolverReset
    SolverOk SetCell:=Cells(25, Column), MaxMinVal:=3, ValueOf:="0", ByChange:=Range(Cells(26, Column), Cells(30, Column))
    SolverAdd CellRef:=Cells(38, Column), Relation:=2, FormulaText:="0"
    SolverAdd CellRef:=Cells(39, Column), Relation:=2, FormulaText:="0"
    SolverOk SetCell:=Cells(25, Column), MaxMinVal:=3, ValueOf:="0", ByChange:=Range(Cells(26, Column), Cells(30, Column))
    SolverSolve UserFinish:=True

    Next Counter


    Reply With Quote  
     

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