Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set solver constraint not working in VB
Greetings all. I've got a solver macro, which successfully sets two of three
required constraints by using variable ranges in the SolverAdd statement, like bellow, where LastInputRow and ThetaCol are integer variables. ' Set the input range constraint SolverAdd CellRef:=Sheets("MacroDEA").Range(Sheets("MacroDEA ") _ .Cells(2, ThetaCol + 1).Address & ":" & _ Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + 1) _ .Address), Relation:=1, formulatext:= _ Sheets("MacroDEA").Range(Sheets("MacroDEA") _ .Cells(2, ThetaCol + 3).Address & ":" & _ Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + 3).Address) The above code is equivalent to... SolverAdd CellRef:="$BV$2:$BV$6", Relation:=1, formulatext:="$BX$2:$BX$6", and it works without a hitch. I have two range constraints set up like this, and both are added to solver no problem. However, I also have a third constraint that is only one cell, and I need to also use variable cell ranges. The below is the hard coded constraint, which is added to the constraints no problem... SolverAdd CellRef:="$BV$10", Relation:=2, formulatext:="$BX$10" When I try to use the same set up for the third constraint, it does not work, like this... SolverAdd CellRef:=Sheets("MacroDEA").Cells(LastDataRow + 1, ThetaCol + 1), _ Relation:=2, formulatext:=1 I tried naming the cell's address as a variable and using the variable, but that did not work either. The only thing I can do to make it stick is hard code it, which will not work for my set up. I am completely stumped over this third constraint. Any ideas? Thank you Greg Snidow |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set solver constraint not working in VB
Greg Snidow,
Ultimately, it looks like your range objects are missing a final ")". I've provided a slightly different approach below. It's not tested, so if Solver wants a string input, i.e. .Address, as opposed to a Range Object, then simply tack a .Address on the end of the range objects. Best, Matthew Herbert Dim Wks As Worksheet Dim rngCellRef As Range Dim intRelation As Integer Dim rngFormulaText As Range Set Wks = Sheets("MacroDEA") With Wks Set rngCellRef = Range(.Cells(2, ThetaCol + 1), _ .Cells(LastInputRow, ThetaCol + 1)) Set rngFormulaText = Range(.Cells(2, ThetaCol + 3), _ .Cells(LastInputRow, ThetaCol + 3)) End With intRelation = 1 SolverAdd CellRef:=rngCellRef, _ Relation:=intRelation, _ FormulaText:=rngFormulaText "Greg Snidow" wrote: Greetings all. I've got a solver macro, which successfully sets two of three required constraints by using variable ranges in the SolverAdd statement, like bellow, where LastInputRow and ThetaCol are integer variables. ' Set the input range constraint SolverAdd CellRef:=Sheets("MacroDEA").Range(Sheets("MacroDEA ") _ .Cells(2, ThetaCol + 1).Address & ":" & _ Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + 1) _ .Address), Relation:=1, formulatext:= _ Sheets("MacroDEA").Range(Sheets("MacroDEA") _ .Cells(2, ThetaCol + 3).Address & ":" & _ Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + 3).Address) The above code is equivalent to... SolverAdd CellRef:="$BV$2:$BV$6", Relation:=1, formulatext:="$BX$2:$BX$6", and it works without a hitch. I have two range constraints set up like this, and both are added to solver no problem. However, I also have a third constraint that is only one cell, and I need to also use variable cell ranges. The below is the hard coded constraint, which is added to the constraints no problem... SolverAdd CellRef:="$BV$10", Relation:=2, formulatext:="$BX$10" When I try to use the same set up for the third constraint, it does not work, like this... SolverAdd CellRef:=Sheets("MacroDEA").Cells(LastDataRow + 1, ThetaCol + 1), _ Relation:=2, formulatext:=1 I tried naming the cell's address as a variable and using the variable, but that did not work either. The only thing I can do to make it stick is hard code it, which will not work for my set up. I am completely stumped over this third constraint. Any ideas? Thank you Greg Snidow |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set solver constraint not working in VB
Greg Snidow,
I initially read through your post too quickly and didn't fully address your question. Try using the range objects listed in my initial post and alter them accordingly for each of the three constraints. See if this will solve the issue, or at least help point out where the problem resides. Best, Matthew Herbert Dim Wks As Worksheet Dim rngCellRef As Range Dim intRelation As Integer Dim rngFormulaText As Range Set Wks = Sheets("MacroDEA") '------------------------------------------------------------------- 'Constraint 1 With Wks Set rngCellRef = Range(.Cells(2, ThetaCol + 1), _ .Cells(LastInputRow, ThetaCol + 1)) Set rngFormulaText = Range(.Cells(2, ThetaCol + 3), _ .Cells(LastInputRow, ThetaCol + 3)) End With intRelation = 1 SolverAdd CellRef:=rngCellRef, _ Relation:=intRelation, _ FormulaText:=rngFormulaText '------------------------------------------------------------------- '------------------------------------------------------------------- 'Constraint 2 'Add accordingly '------------------------------------------------------------------- '------------------------------------------------------------------- 'Constraint 3 With Wks Set rngCellRef = .Cells(LastDataRow + 1, ThetaCol + 1) 'uncomment this and change if the FormulaText references a range 'Set rngFormulaText = .Cells(Row #, Column #) End With intRelation = 2 'add in rngFormulaText if uncommented from above SolverAdd CellRef:=rngCellRef, _ Relation:=intRelation, _ FormulaText:=1 '------------------------------------------------------------------- "Matthew Herbert" wrote: Greg Snidow, Ultimately, it looks like your range objects are missing a final ")". I've provided a slightly different approach below. It's not tested, so if Solver wants a string input, i.e. .Address, as opposed to a Range Object, then simply tack a .Address on the end of the range objects. Best, Matthew Herbert Dim Wks As Worksheet Dim rngCellRef As Range Dim intRelation As Integer Dim rngFormulaText As Range Set Wks = Sheets("MacroDEA") With Wks Set rngCellRef = Range(.Cells(2, ThetaCol + 1), _ .Cells(LastInputRow, ThetaCol + 1)) Set rngFormulaText = Range(.Cells(2, ThetaCol + 3), _ .Cells(LastInputRow, ThetaCol + 3)) End With intRelation = 1 SolverAdd CellRef:=rngCellRef, _ Relation:=intRelation, _ FormulaText:=rngFormulaText "Greg Snidow" wrote: Greetings all. I've got a solver macro, which successfully sets two of three required constraints by using variable ranges in the SolverAdd statement, like bellow, where LastInputRow and ThetaCol are integer variables. ' Set the input range constraint SolverAdd CellRef:=Sheets("MacroDEA").Range(Sheets("MacroDEA ") _ .Cells(2, ThetaCol + 1).Address & ":" & _ Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + 1) _ .Address), Relation:=1, formulatext:= _ Sheets("MacroDEA").Range(Sheets("MacroDEA") _ .Cells(2, ThetaCol + 3).Address & ":" & _ Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + 3).Address) The above code is equivalent to... SolverAdd CellRef:="$BV$2:$BV$6", Relation:=1, formulatext:="$BX$2:$BX$6", and it works without a hitch. I have two range constraints set up like this, and both are added to solver no problem. However, I also have a third constraint that is only one cell, and I need to also use variable cell ranges. The below is the hard coded constraint, which is added to the constraints no problem... SolverAdd CellRef:="$BV$10", Relation:=2, formulatext:="$BX$10" When I try to use the same set up for the third constraint, it does not work, like this... SolverAdd CellRef:=Sheets("MacroDEA").Cells(LastDataRow + 1, ThetaCol + 1), _ Relation:=2, formulatext:=1 I tried naming the cell's address as a variable and using the variable, but that did not work either. The only thing I can do to make it stick is hard code it, which will not work for my set up. I am completely stumped over this third constraint. Any ideas? Thank you Greg Snidow |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set solver constraint not working in VB
Mathew, thank you for your suggestions. I am almost ready to give it a go,
but I wanted to throw something out there. I ended up reversing my data set up, which meant I had to slightly alter almost 1000 lines of code, so that the constraint ranges would not change, so I no longer need variables for them in the Solver part. I thought I was good to go until I tried it. Now, with the hard coded constraint ranges, all three constraints are added with no problems. However, now the *same* variable I *was* using for the ByChange range no longer works, and it only works if I hard code *it*. I will let you know how it comes out. I'm just learning how to use range variable in a With block, so it might take me a little while. Thanks again. Greg "Matthew Herbert" wrote: Greg Snidow, I initially read through your post too quickly and didn't fully address your question. Try using the range objects listed in my initial post and alter them accordingly for each of the three constraints. See if this will solve the issue, or at least help point out where the problem resides. Best, Matthew Herbert Dim Wks As Worksheet Dim rngCellRef As Range Dim intRelation As Integer Dim rngFormulaText As Range Set Wks = Sheets("MacroDEA") '------------------------------------------------------------------- 'Constraint 1 With Wks Set rngCellRef = Range(.Cells(2, ThetaCol + 1), _ .Cells(LastInputRow, ThetaCol + 1)) Set rngFormulaText = Range(.Cells(2, ThetaCol + 3), _ .Cells(LastInputRow, ThetaCol + 3)) End With intRelation = 1 SolverAdd CellRef:=rngCellRef, _ Relation:=intRelation, _ FormulaText:=rngFormulaText '------------------------------------------------------------------- '------------------------------------------------------------------- 'Constraint 2 'Add accordingly '------------------------------------------------------------------- '------------------------------------------------------------------- 'Constraint 3 With Wks Set rngCellRef = .Cells(LastDataRow + 1, ThetaCol + 1) 'uncomment this and change if the FormulaText references a range 'Set rngFormulaText = .Cells(Row #, Column #) End With intRelation = 2 'add in rngFormulaText if uncommented from above SolverAdd CellRef:=rngCellRef, _ Relation:=intRelation, _ FormulaText:=1 '------------------------------------------------------------------- "Matthew Herbert" wrote: Greg Snidow, Ultimately, it looks like your range objects are missing a final ")". I've provided a slightly different approach below. It's not tested, so if Solver wants a string input, i.e. .Address, as opposed to a Range Object, then simply tack a .Address on the end of the range objects. Best, Matthew Herbert Dim Wks As Worksheet Dim rngCellRef As Range Dim intRelation As Integer Dim rngFormulaText As Range Set Wks = Sheets("MacroDEA") With Wks Set rngCellRef = Range(.Cells(2, ThetaCol + 1), _ .Cells(LastInputRow, ThetaCol + 1)) Set rngFormulaText = Range(.Cells(2, ThetaCol + 3), _ .Cells(LastInputRow, ThetaCol + 3)) End With intRelation = 1 SolverAdd CellRef:=rngCellRef, _ Relation:=intRelation, _ FormulaText:=rngFormulaText "Greg Snidow" wrote: Greetings all. I've got a solver macro, which successfully sets two of three required constraints by using variable ranges in the SolverAdd statement, like bellow, where LastInputRow and ThetaCol are integer variables. ' Set the input range constraint SolverAdd CellRef:=Sheets("MacroDEA").Range(Sheets("MacroDEA ") _ .Cells(2, ThetaCol + 1).Address & ":" & _ Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + 1) _ .Address), Relation:=1, formulatext:= _ Sheets("MacroDEA").Range(Sheets("MacroDEA") _ .Cells(2, ThetaCol + 3).Address & ":" & _ Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + 3).Address) The above code is equivalent to... SolverAdd CellRef:="$BV$2:$BV$6", Relation:=1, formulatext:="$BX$2:$BX$6", and it works without a hitch. I have two range constraints set up like this, and both are added to solver no problem. However, I also have a third constraint that is only one cell, and I need to also use variable cell ranges. The below is the hard coded constraint, which is added to the constraints no problem... SolverAdd CellRef:="$BV$10", Relation:=2, formulatext:="$BX$10" When I try to use the same set up for the third constraint, it does not work, like this... SolverAdd CellRef:=Sheets("MacroDEA").Cells(LastDataRow + 1, ThetaCol + 1), _ Relation:=2, formulatext:=1 I tried naming the cell's address as a variable and using the variable, but that did not work either. The only thing I can do to make it stick is hard code it, which will not work for my set up. I am completely stumped over this third constraint. Any ideas? Thank you Greg Snidow |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set solver constraint not working in VB
Greg,
When you use the With Statement, ensure that you qualify correctly. I'll illustrate with a simply example below. (See the explanation after the code). Sub QualifyRanges() Dim wksOne As Worksheet Dim wksTwo As Worksheet Dim Rng As Range Set wksOne = Worksheets("Sheet1") Set wksTwo = Worksheets("Sheet2") wksOne.Select With wksTwo .Range("A1").Value = "This is worksheet two." Set Rng = Range("A1:B5") MsgBox Rng.Address(External:=True) .Select End With End Sub The main point here is that the Rng Object is referring to Sheet1 (i.e. the current ActiveSheet by default), not Sheet2. The Set statement creates an object. So, in the code above I created three objects wksOne, wksTwo, and Rng. wksOne and wksTwo are Worksheet objects and Rng is a Range object. Thus, wksOne and wksTwo take on all the attributes available to Worksheets and Rng takes on all the attributes available to Range. Cool, huh? The With statement allows you to run a series of statements for one object. So, With the wksTwo object, perform all the . attributes (i.e. you get the benefit of Intellisence popping up when you type the dot operator). For example, its as if the .Select in the With statement were wksTwo.Select. With statements help prevent errors, decrease the amount of typing that is needed, and help with code compilation; however, you fall into the potential trap of not applying a . appropriately, like with the Set Rng line of code above. The Set Rng line of code is missing the . before Range("A1:B5"). Thus, when the program gets to this line of code, the default hierarchy is the active workbook and the active worksheet. Even though the Set Rng statement is embedded in the With statement, Rng is set to a range on Sheet1, not Sheet2. Put the . before Range("A1:B5") and rerun the code. (The External parameter of the Address property returns the fully qualified address of the range). Below are the message box results of the two scenarios: - Set Rng = Range("A1:B5"): [Book1]Sheet1!$A$1:$B$5 - Set Rng = .Range("A1:B5"): [Book1]Sheet2!$A$1:$B$5 Notice the difference in Sheet1! and Sheet2! Best, Matt "Greg Snidow" wrote: Mathew, thank you for your suggestions. I am almost ready to give it a go, but I wanted to throw something out there. I ended up reversing my data set up, which meant I had to slightly alter almost 1000 lines of code, so that the constraint ranges would not change, so I no longer need variables for them in the Solver part. I thought I was good to go until I tried it. Now, with the hard coded constraint ranges, all three constraints are added with no problems. However, now the *same* variable I *was* using for the ByChange range no longer works, and it only works if I hard code *it*. I will let you know how it comes out. I'm just learning how to use range variable in a With block, so it might take me a little while. Thanks again. Greg "Matthew Herbert" wrote: Greg Snidow, I initially read through your post too quickly and didn't fully address your question. Try using the range objects listed in my initial post and alter them accordingly for each of the three constraints. See if this will solve the issue, or at least help point out where the problem resides. Best, Matthew Herbert Dim Wks As Worksheet Dim rngCellRef As Range Dim intRelation As Integer Dim rngFormulaText As Range Set Wks = Sheets("MacroDEA") '------------------------------------------------------------------- 'Constraint 1 With Wks Set rngCellRef = Range(.Cells(2, ThetaCol + 1), _ .Cells(LastInputRow, ThetaCol + 1)) Set rngFormulaText = Range(.Cells(2, ThetaCol + 3), _ .Cells(LastInputRow, ThetaCol + 3)) End With intRelation = 1 SolverAdd CellRef:=rngCellRef, _ Relation:=intRelation, _ FormulaText:=rngFormulaText '------------------------------------------------------------------- '------------------------------------------------------------------- 'Constraint 2 'Add accordingly '------------------------------------------------------------------- '------------------------------------------------------------------- 'Constraint 3 With Wks Set rngCellRef = .Cells(LastDataRow + 1, ThetaCol + 1) 'uncomment this and change if the FormulaText references a range 'Set rngFormulaText = .Cells(Row #, Column #) End With intRelation = 2 'add in rngFormulaText if uncommented from above SolverAdd CellRef:=rngCellRef, _ Relation:=intRelation, _ FormulaText:=1 '------------------------------------------------------------------- "Matthew Herbert" wrote: Greg Snidow, Ultimately, it looks like your range objects are missing a final ")". I've provided a slightly different approach below. It's not tested, so if Solver wants a string input, i.e. .Address, as opposed to a Range Object, then simply tack a .Address on the end of the range objects. Best, Matthew Herbert Dim Wks As Worksheet Dim rngCellRef As Range Dim intRelation As Integer Dim rngFormulaText As Range Set Wks = Sheets("MacroDEA") With Wks Set rngCellRef = Range(.Cells(2, ThetaCol + 1), _ .Cells(LastInputRow, ThetaCol + 1)) Set rngFormulaText = Range(.Cells(2, ThetaCol + 3), _ .Cells(LastInputRow, ThetaCol + 3)) End With intRelation = 1 SolverAdd CellRef:=rngCellRef, _ Relation:=intRelation, _ FormulaText:=rngFormulaText "Greg Snidow" wrote: Greetings all. I've got a solver macro, which successfully sets two of three required constraints by using variable ranges in the SolverAdd statement, like bellow, where LastInputRow and ThetaCol are integer variables. ' Set the input range constraint SolverAdd CellRef:=Sheets("MacroDEA").Range(Sheets("MacroDEA ") _ .Cells(2, ThetaCol + 1).Address & ":" & _ Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + 1) _ .Address), Relation:=1, formulatext:= _ Sheets("MacroDEA").Range(Sheets("MacroDEA") _ .Cells(2, ThetaCol + 3).Address & ":" & _ Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + 3).Address) The above code is equivalent to... SolverAdd CellRef:="$BV$2:$BV$6", Relation:=1, formulatext:="$BX$2:$BX$6", and it works without a hitch. I have two range constraints set up like this, and both are added to solver no problem. However, I also have a third constraint that is only one cell, and I need to also use variable cell ranges. The below is the hard coded constraint, which is added to the constraints no problem... SolverAdd CellRef:="$BV$10", Relation:=2, formulatext:="$BX$10" When I try to use the same set up for the third constraint, it does not work, like this... SolverAdd CellRef:=Sheets("MacroDEA").Cells(LastDataRow + 1, ThetaCol + 1), _ Relation:=2, formulatext:=1 I tried naming the cell's address as a variable and using the variable, but that did not work either. The only thing I can do to make it stick is hard code it, which will not work for my set up. I am completely stumped over this third constraint. Any ideas? Thank you Greg Snidow |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set solver constraint not working in VB
Matt, thank you so much for the thorough explaination of the using with
blocks, and that ranges and worksheets are treated as objects. I've been messing around with using with blocks for a couple of weeks, but I have never come accross a meaninful explaination. However, I never really looked hard. Anyhow, I knew something was different about ranges, because you can't do MsgBox(Range(variable)). I have definitely benefitted from your input here. On my issue, though, all is good now. I got some weird error one time while running the macro that said something like "Solver ran out of memory..." or something like that. So I tried to run Solver one time from the toolbar, and same thing. I ended up making a new workbook, copying and pasting the code, and voila, all three constraints worked as variables again, as I knew they should have done in the first place. Thanks for the time you took out of your day to consider my problem, I very much appreciate all the help I get here. Greg "Matthew Herbert" wrote: Greg, When you use the With Statement, ensure that you qualify correctly. I'll illustrate with a simply example below. (See the explanation after the code). Sub QualifyRanges() Dim wksOne As Worksheet Dim wksTwo As Worksheet Dim Rng As Range Set wksOne = Worksheets("Sheet1") Set wksTwo = Worksheets("Sheet2") wksOne.Select With wksTwo .Range("A1").Value = "This is worksheet two." Set Rng = Range("A1:B5") MsgBox Rng.Address(External:=True) .Select End With End Sub The main point here is that the Rng Object is referring to Sheet1 (i.e. the current ActiveSheet by default), not Sheet2. The Set statement creates an object. So, in the code above I created three objects wksOne, wksTwo, and Rng. wksOne and wksTwo are Worksheet objects and Rng is a Range object. Thus, wksOne and wksTwo take on all the attributes available to Worksheets and Rng takes on all the attributes available to Range. Cool, huh? The With statement allows you to run a series of statements for one object. So, With the wksTwo object, perform all the . attributes (i.e. you get the benefit of Intellisence popping up when you type the dot operator). For example, its as if the .Select in the With statement were wksTwo.Select. With statements help prevent errors, decrease the amount of typing that is needed, and help with code compilation; however, you fall into the potential trap of not applying a . appropriately, like with the Set Rng line of code above. The Set Rng line of code is missing the . before Range("A1:B5"). Thus, when the program gets to this line of code, the default hierarchy is the active workbook and the active worksheet. Even though the Set Rng statement is embedded in the With statement, Rng is set to a range on Sheet1, not Sheet2. Put the . before Range("A1:B5") and rerun the code. (The External parameter of the Address property returns the fully qualified address of the range). Below are the message box results of the two scenarios: - Set Rng = Range("A1:B5"): [Book1]Sheet1!$A$1:$B$5 - Set Rng = .Range("A1:B5"): [Book1]Sheet2!$A$1:$B$5 Notice the difference in Sheet1! and Sheet2! Best, Matt "Greg Snidow" wrote: Mathew, thank you for your suggestions. I am almost ready to give it a go, but I wanted to throw something out there. I ended up reversing my data set up, which meant I had to slightly alter almost 1000 lines of code, so that the constraint ranges would not change, so I no longer need variables for them in the Solver part. I thought I was good to go until I tried it. Now, with the hard coded constraint ranges, all three constraints are added with no problems. However, now the *same* variable I *was* using for the ByChange range no longer works, and it only works if I hard code *it*. I will let you know how it comes out. I'm just learning how to use range variable in a With block, so it might take me a little while. Thanks again. Greg "Matthew Herbert" wrote: Greg Snidow, I initially read through your post too quickly and didn't fully address your question. Try using the range objects listed in my initial post and alter them accordingly for each of the three constraints. See if this will solve the issue, or at least help point out where the problem resides. Best, Matthew Herbert Dim Wks As Worksheet Dim rngCellRef As Range Dim intRelation As Integer Dim rngFormulaText As Range Set Wks = Sheets("MacroDEA") '------------------------------------------------------------------- 'Constraint 1 With Wks Set rngCellRef = Range(.Cells(2, ThetaCol + 1), _ .Cells(LastInputRow, ThetaCol + 1)) Set rngFormulaText = Range(.Cells(2, ThetaCol + 3), _ .Cells(LastInputRow, ThetaCol + 3)) End With intRelation = 1 SolverAdd CellRef:=rngCellRef, _ Relation:=intRelation, _ FormulaText:=rngFormulaText '------------------------------------------------------------------- '------------------------------------------------------------------- 'Constraint 2 'Add accordingly '------------------------------------------------------------------- '------------------------------------------------------------------- 'Constraint 3 With Wks Set rngCellRef = .Cells(LastDataRow + 1, ThetaCol + 1) 'uncomment this and change if the FormulaText references a range 'Set rngFormulaText = .Cells(Row #, Column #) End With intRelation = 2 'add in rngFormulaText if uncommented from above SolverAdd CellRef:=rngCellRef, _ Relation:=intRelation, _ FormulaText:=1 '------------------------------------------------------------------- "Matthew Herbert" wrote: Greg Snidow, Ultimately, it looks like your range objects are missing a final ")". I've provided a slightly different approach below. It's not tested, so if Solver wants a string input, i.e. .Address, as opposed to a Range Object, then simply tack a .Address on the end of the range objects. Best, Matthew Herbert Dim Wks As Worksheet Dim rngCellRef As Range Dim intRelation As Integer Dim rngFormulaText As Range Set Wks = Sheets("MacroDEA") With Wks Set rngCellRef = Range(.Cells(2, ThetaCol + 1), _ .Cells(LastInputRow, ThetaCol + 1)) Set rngFormulaText = Range(.Cells(2, ThetaCol + 3), _ .Cells(LastInputRow, ThetaCol + 3)) End With intRelation = 1 SolverAdd CellRef:=rngCellRef, _ Relation:=intRelation, _ FormulaText:=rngFormulaText "Greg Snidow" wrote: Greetings all. I've got a solver macro, which successfully sets two of three required constraints by using variable ranges in the SolverAdd statement, like bellow, where LastInputRow and ThetaCol are integer variables. ' Set the input range constraint SolverAdd CellRef:=Sheets("MacroDEA").Range(Sheets("MacroDEA ") _ .Cells(2, ThetaCol + 1).Address & ":" & _ Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + 1) _ .Address), Relation:=1, formulatext:= _ Sheets("MacroDEA").Range(Sheets("MacroDEA") _ .Cells(2, ThetaCol + 3).Address & ":" & _ Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + 3).Address) The above code is equivalent to... SolverAdd CellRef:="$BV$2:$BV$6", Relation:=1, formulatext:="$BX$2:$BX$6", and it works without a hitch. I have two range constraints set up like this, and both are added to solver no problem. However, I also have a third constraint that is only one cell, and I need to also use variable cell ranges. The below is the hard coded constraint, which is added to the constraints no problem... SolverAdd CellRef:="$BV$10", Relation:=2, formulatext:="$BX$10" When I try to use the same set up for the third constraint, it does not work, like this... SolverAdd CellRef:=Sheets("MacroDEA").Cells(LastDataRow + 1, ThetaCol + 1), _ Relation:=2, formulatext:=1 I tried naming the cell's address as a variable and using the variable, but that did not work either. The only thing I can do to make it stick is hard code it, which will not work for my set up. I am completely stumped over this third constraint. Any ideas? Thank you Greg Snidow |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set solver constraint not working in VB
SolverAdd CellRef:=Sheets("MacroDEA").Range(Sheets("MacroDEA ") _
.Cells(2, ThetaCol + 1).Address & ":" & _ Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + 1) _ .Address), Relation:=1, formulatext:= _ Sheets("MacroDEA").Range(Sheets("MacroDEA") _ .Cells(2, ThetaCol + 3).Address & ":" & _ Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + Hi. Not any better, but see if there are any ideas here you could use. Sub Demo() Const ThetaCol As Long = 73 Const LastInputRow As Long = 6 Const LastDataRow As Long = 9 Dim LHS As Range Dim RHS As Range Dim NR As Long 'Number of Rows 'Sheet must be active for Solver... Sheets("MacroDEA").Activate 'Number of Rows NR = LastInputRow - 2 + 1 Set LHS = Cells(2, ThetaCol + 1).Resize(NR) 'RHS is 2 Columns over... Set RHS = LHS.Offset(0, 2) 'Start a clean Solver... SolverReset 'Add SolverOk, etc... SolverAdd LHS.Address, 1, RHS.Address SolverAdd Cells(LastDataRow + 1, ThetaCol + 1), 2, 1 End Sub = = = = = = = = = = = HTH Dana DeLouis Note: I don't know your model, but be careful if BV10 is a "Sum()" formula that must equal 1, due to precision / tolerance of Solver. Greg Snidow wrote: Greetings all. I've got a solver macro, which successfully sets two of three required constraints by using variable ranges in the SolverAdd statement, like bellow, where LastInputRow and ThetaCol are integer variables. ' Set the input range constraint SolverAdd CellRef:=Sheets("MacroDEA").Range(Sheets("MacroDEA ") _ .Cells(2, ThetaCol + 1).Address & ":" & _ Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + 1) _ .Address), Relation:=1, formulatext:= _ Sheets("MacroDEA").Range(Sheets("MacroDEA") _ .Cells(2, ThetaCol + 3).Address & ":" & _ Sheets("MacroDEA").Cells(LastInputRow, ThetaCol + 3).Address) The above code is equivalent to... SolverAdd CellRef:="$BV$2:$BV$6", Relation:=1, formulatext:="$BX$2:$BX$6", and it works without a hitch. I have two range constraints set up like this, and both are added to solver no problem. However, I also have a third constraint that is only one cell, and I need to also use variable cell ranges. The below is the hard coded constraint, which is added to the constraints no problem... SolverAdd CellRef:="$BV$10", Relation:=2, formulatext:="$BX$10" When I try to use the same set up for the third constraint, it does not work, like this... SolverAdd CellRef:=Sheets("MacroDEA").Cells(LastDataRow + 1, ThetaCol + 1), _ Relation:=2, formulatext:=1 I tried naming the cell's address as a variable and using the variable, but that did not work either. The only thing I can do to make it stick is hard code it, which will not work for my set up. I am completely stumped over this third constraint. Any ideas? Thank you Greg Snidow |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Solver with Linear Constraint(s) | Excel Programming | |||
solver result and constraint CHART | Charts and Charting in Excel | |||
Add Constraint - "Solver: | Excel Discussion (Misc queries) | |||
solver constraint | Excel Worksheet Functions | |||
Setting a Solver constraint using vba? | Excel Programming |