Answered by:
VBA Solver with variables when referencing
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() 'Subprocedure 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 SubWould 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
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
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