none
HELP please with simple VBA RRS feed

  • Question

  • Hello I just need some help with a simple VBA macro. I tried to record a macro and got this:

    Sub Solver()
    '
    ' Solver Macro
    '

    '
        SolverOk SetCell:="$P$260", MaxMinVal:=1, ValueOf:="0", ByChange:="$L$260"
        SolverAdd CellRef:="$L$260", Relation:=1, FormulaText:="1"
        SolverOk SetCell:="$P$260", MaxMinVal:=1, ValueOf:="0", ByChange:="$L$260"
        SolverDelete CellRef:="$L$258", Relation:=3, FormulaText:="0"
        SolverAdd CellRef:="$L$260", Relation:=3, FormulaText:="0"
        SolverOk SetCell:="$P$260", MaxMinVal:=1, ValueOf:="0", ByChange:="$L$260"
        SolverSolve
    End Sub

    So I recorded the macro because I Do not really know how to code. But my intent was to maximize a cell on the P column from the ranges (P255:P2520) while only changing the value of the corresponding  L cell in the same row. But clearly the macro can only do it for one cell. I need it to continue for the rest of the column. How can I automate the process by creating a loop so excel uses solver to maximize all P cell from (P255:P2520) by only changing the L cell in the same row and while keeping the constraints of being between 0 and 1. A simple code to make it  easy to understand would be much appreciated.Thanks you.






    • Edited by cantprogram Wednesday, November 27, 2013 7:18 AM
    Wednesday, November 27, 2013 7:17 AM

Answers

  • Try this:

    Sub SolveAll()
        Dim r As Long
        Application.ScreenUpdating = False
        For r = 255 To 2520
            SolverReset
            SolverOk SetCell:="$P$" & r, MaxMinVal:=1, ValueOf:=0, _
                ByChange:="$L$" & r
            SolverAdd CellRef:="$L$" & r, Relation:=3, FormulaText:="0"
            SolverAdd CellRef:="$L$" & r, Relation:=1, FormulaText:="1"
            SolverSolve UserFinish:=True
        Next r
        Application.ScreenUpdating = True
    End Sub

    Calling Solver over 2200 times might take a while!


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by cantprogram Wednesday, November 27, 2013 7:29 PM
    Wednesday, November 27, 2013 8:58 AM