Hello
I need to get solver to maximize n different functions and in each maximization problem, I need solver to refer to three different cells in a sheet as the changing cells.
I thought I had figured out how to make solver refer to the different cells using the intersect method for defining a range, but it's working very strangely.
It's identifying the correct range, but when I put it in the solver function, instead of changing the cells in the range, it prints the range in the cells in the range!
That is, instead of changing the values in cells H1:H3, it prints "$H$1:$H$3" in cells H1:H3.
It is properly scrolling through the columns that I want it to, however.
Here's the code, and any help appreciated. Thanks!
Sub maxliklihood()
Dim n As Integer, i As Integer, j As Integer, m As Integer, r1 As Range, r2 As Range, intrsct As Range
n = Worksheets("data").Cells(1, 3)
For j = 1 To n
Set r1 = Union(Rows(1), Rows(2), Rows(3))
Set r2 = Columns(6 + 2 * j)
Set intrsct = Application.Intersect(r1, r2)
solverreset
solveradd cellref:=Worksheets("data").Cells(4, 6 + 2 * j), relation:=1, FormulaText:="0.9999"
SolverOptions MaxTime:=100, Iterations:=1000, Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True
solverok setcell:=Worksheets("data").Cells(5, 7 + 2 * j), MaxMinVal:=1, Valueof:="0", ByChange:=intrsct
solversolve True
Next j
End Sub
If I use the more common syntax "ByChange:=Range("intrsct"), I get a "Run-time error '1004': Application-defined or object-defined error"
Thanks much
I need to get solver to maximize n different functions and in each maximization problem, I need solver to refer to three different cells in a sheet as the changing cells.
I thought I had figured out how to make solver refer to the different cells using the intersect method for defining a range, but it's working very strangely.
It's identifying the correct range, but when I put it in the solver function, instead of changing the cells in the range, it prints the range in the cells in the range!
That is, instead of changing the values in cells H1:H3, it prints "$H$1:$H$3" in cells H1:H3.
It is properly scrolling through the columns that I want it to, however.
Here's the code, and any help appreciated. Thanks!
Sub maxliklihood()
Dim n As Integer, i As Integer, j As Integer, m As Integer, r1 As Range, r2 As Range, intrsct As Range
n = Worksheets("data").Cells(1, 3)
For j = 1 To n
Set r1 = Union(Rows(1), Rows(2), Rows(3))
Set r2 = Columns(6 + 2 * j)
Set intrsct = Application.Intersect(r1, r2)
solverreset
solveradd cellref:=Worksheets("data").Cells(4, 6 + 2 * j), relation:=1, FormulaText:="0.9999"
SolverOptions MaxTime:=100, Iterations:=1000, Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True
solverok setcell:=Worksheets("data").Cells(5, 7 + 2 * j), MaxMinVal:=1, Valueof:="0", ByChange:=intrsct
solversolve True
Next j
End Sub
If I use the more common syntax "ByChange:=Range("intrsct"), I get a "Run-time error '1004': Application-defined or object-defined error"
Thanks much
