locked
VBA Solver with variables when referencing RRS feed

  • Question

  • I am attempting to run a sub using solver that references cells based on the values of certain variables (P & R). The cells referenced would correspond by column. Meaning that when I am taking the SUMPRODUCT then I want the value in one row to be multiplied by the value in another row of the same column.

    When I run the procedure solver displays a "Solver Results" window that indicates an error.

    This error reads: "Error in model. Please verify that all cells and Constraints are valid. Perhaps some cells that are not VariableCells are marked as Integer, Binary or All Different."

    Here is my code:

    Sub Solve_It()      'Sub-procedure to call solver and its functions

        Dim ProRng As Range             'Range that holds the decision variables
        Dim PayRng As Range             'Range that holds the p values
        Dim ResRng As Range             'Range that hold the r requirements
        Dim i As Long                          'Variable used for solver loop of constraints
        
        P = Range("B1").Value
        R = Range("B2").Value
        
        Set PayRng = Sheet1.Range(Cells(6, 1), Cells(6, P)) 'Sets the range for the pay cells
        Set ProRng = Sheet1.Range(Cells(8, 1), Cells(8, P)) 'Sets the range for the pro cells, (binary in solver)
        
        'Setting the formula of cell B3 equal to the sum of the product of the pay cells
        'and the decision variables
        Sheet1.Range("B3").Formula = "=SumProduct(PayRng, ProRng)"
     
        
        Application.ScreenUpdating = False
        Worksheets("Sheet1").Activate
        SolverReset
        SolverOptions Precision:=0.001
        
        SolverOk SetCell:=Range(Cells(3, 2), Cells(3, 2)).Address, _
            MaxMinVal:=1, _
            ByChange:=ProRng.Select, _
            Engine:=1
        
        SolverAdd ProRng, 5  'Constraint making the decision variables binary
        
        For i = 1 To R  'Loop that adds constraints for the number of R types
            Set ResRng = Sheet1.Range(Cells(11 + i, 1), Cells(11 + i, P))
            Sheet1.Range(Cells(4, i), Cells(4, i)).Formula = "=SumProduct(ResRng, ProRng)"
            SolverAdd Cells(4, i), 1, Cells(10, i)
        Next i

        SolverSolve UserFinish:=False
        SolverFinish KeepFinal:=1
    End Sub

    Would greatly appreciate any help that I could get with this.

    Wednesday, September 17, 2014 3:46 PM

Answers

  • The first error that jumps out is the incorrect use of range objects. If PayRng and ProRng were the names of ranges defined in the workbook, then this would work.

    Sheet1.Range("B3").Formula = "=SumProduct(PayRng, ProRng)"

    But both are declared as ranges, so you need to create a viable formula from them, like so:

        Sheet1.Range("B3").Formula = "=SumProduct(" & PayRng.Address & "," & ProRng.Address & ")"

    Further, you are sometimes using Sheet1.Range and sometimes not, which could lead to huge errors (not so much if you only have one sheet) - as in this line:

        Set PayRng = Sheet1.Range(Cells(6, 1), Cells(6, P)) 'Sets the range for the pay cells

    Still, it is good practice to either write for a specific sheet, where you fully qualify all range references

        Set PayRng = Sheet1.Range(Sheet1.Cells(6, 1), Sheet1.Cells(6, P)) 'Sets the range for the pay cells on Sheet1

    or to write for the active sheet:

        Set PayRng = Range(Cells(6, 1), Cells(6, P)) 'Sets the range for the pay cells using the active sheet

    • Proposed as answer by danishani Wednesday, September 17, 2014 10:41 PM
    • Marked as answer by danishani Monday, September 22, 2014 3:48 PM
    Wednesday, September 17, 2014 6:58 PM

All replies

  • The first error that jumps out is the incorrect use of range objects. If PayRng and ProRng were the names of ranges defined in the workbook, then this would work.

    Sheet1.Range("B3").Formula = "=SumProduct(PayRng, ProRng)"

    But both are declared as ranges, so you need to create a viable formula from them, like so:

        Sheet1.Range("B3").Formula = "=SumProduct(" & PayRng.Address & "," & ProRng.Address & ")"

    Further, you are sometimes using Sheet1.Range and sometimes not, which could lead to huge errors (not so much if you only have one sheet) - as in this line:

        Set PayRng = Sheet1.Range(Cells(6, 1), Cells(6, P)) 'Sets the range for the pay cells

    Still, it is good practice to either write for a specific sheet, where you fully qualify all range references

        Set PayRng = Sheet1.Range(Sheet1.Cells(6, 1), Sheet1.Cells(6, P)) 'Sets the range for the pay cells on Sheet1

    or to write for the active sheet:

        Set PayRng = Range(Cells(6, 1), Cells(6, P)) 'Sets the range for the pay cells using the active sheet

    • Proposed as answer by danishani Wednesday, September 17, 2014 10:41 PM
    • Marked as answer by danishani Monday, September 22, 2014 3:48 PM
    Wednesday, September 17, 2014 6:58 PM
  • Thank you for your help.  The proper syntax for the  formula/cell references has resolved my issues. I will be accurately qualifying my references in future code. Once again, thank you.
    Wednesday, September 17, 2014 10:36 PM