Home 
Search 
Today's Posts 
#1




Automate a Solver solution
I need to calculate a value to minimize a formula  I can do this with
Solver. The problem: I have 400 rows for which I have to use the Solver calculation  and I need to update it occasionally. I'm trying to figure out how to do this automatically instead of solving each line manually. I suppose I could write a macro, but I don't know how to make the macro repeat itself until the last row and then stop. Any ideas? Thanks.  Janet 
#2




Automate a Solver solution
Maybe something like this will get you started, where the value you want to
insert in the cells is in F1 and the 400 cells are D1400 Sub ForHunRow() Dim i As Integer Dim j As Range, c As Range i = Range("F1").Value Set j = Range("D1400") For Each c In j c.Value = i Next End Sub HTH Regards, Howard "Janet" wrote in message ... I need to calculate a value to minimize a formula  I can do this with Solver. The problem: I have 400 rows for which I have to use the Solver calculation  and I need to update it occasionally. I'm trying to figure out how to do this automatically instead of solving each line manually. I suppose I could write a macro, but I don't know how to make the macro repeat itself until the last row and then stop. Any ideas? Thanks.  Janet 
#3




Automate a Solver solution
Well, I assumed I could write a macro for a Solver transaction, but I keep
getting for "SolverOK" in the macro: Compile Error; Sub or Function not defined. That probably means that a macro will not run the Solver for me, right?  Janet "L. Howard Kittle" wrote: Maybe something like this will get you started, where the value you want to insert in the cells is in F1 and the 400 cells are D1400 Sub ForHunRow() Dim i As Integer Dim j As Range, c As Range i = Range("F1").Value Set j = Range("D1400") For Each c In j c.Value = i Next End Sub HTH Regards, Howard "Janet" wrote in message ... I need to calculate a value to minimize a formula  I can do this with Solver. The problem: I have 400 rows for which I have to use the Solver calculation  and I need to update it occasionally. I'm trying to figure out how to do this automatically instead of solving each line manually. I suppose I could write a macro, but I don't know how to make the macro repeat itself until the last row and then stop. Any ideas? Thanks.  Janet . 
#4




Automate a Solver solution
Before you can use Solver functions in VBA, you must establish a reference to
the Solver addin. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select the Solver.xlam check box under Available References. If Solver.xlam does not appear under Available References, click Browse and open Solver.xlam in the \office12\library\Solver subfolder. If you have already installed the Solver addin in Excel, you will probably see it listed under Available References. To install Solver in Excel 2007: If the Solver button does not appear on the Data tab on the Ribbon, click the Microsoft Office Button, Excel Options, AddIns category, and then click the Go button. Then select the Solver AddIn check box, and click OK to install it. Click Yes to confirm that you want to install the Solver addin. For Excel 2003, I think you need SOLVER.XLA. Sorry, but I only have XL2007 available at the moment. The steps should be similar. Hope this helps, Hutch "Janet" wrote: Well, I assumed I could write a macro for a Solver transaction, but I keep getting for "SolverOK" in the macro: Compile Error; Sub or Function not defined. That probably means that a macro will not run the Solver for me, right?  Janet "L. Howard Kittle" wrote: Maybe something like this will get you started, where the value you want to insert in the cells is in F1 and the 400 cells are D1400 Sub ForHunRow() Dim i As Integer Dim j As Range, c As Range i = Range("F1").Value Set j = Range("D1400") For Each c In j c.Value = i Next End Sub HTH Regards, Howard "Janet" wrote in message ... I need to calculate a value to minimize a formula  I can do this with Solver. The problem: I have 400 rows for which I have to use the Solver calculation  and I need to update it occasionally. I'm trying to figure out how to do this automatically instead of solving each line manually. I suppose I could write a macro, but I don't know how to make the macro repeat itself until the last row and then stop. Any ideas? Thanks.  Janet . 
#5




Automate a Solver solution
Thanks!!! That did the trick, but now I don't know how to integrate the
macro I just wrote into the program that Howard gave me so it will repeat itself for the relevant rows: Sub ForHunRow() Dim i As Integer Dim j As Range, c As Range i = Range("F1").Value Set j = Range("D1400") For Each c In j c.Value = i Next End Sub  Janet "Tom Hutchins" wrote: Before you can use Solver functions in VBA, you must establish a reference to the Solver addin. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select the Solver.xlam check box under Available References. If Solver.xlam does not appear under Available References, click Browse and open Solver.xlam in the \office12\library\Solver subfolder. If you have already installed the Solver addin in Excel, you will probably see it listed under Available References. To install Solver in Excel 2007: If the Solver button does not appear on the Data tab on the Ribbon, click the Microsoft Office Button, Excel Options, AddIns category, and then click the Go button. Then select the Solver AddIn check box, and click OK to install it. Click Yes to confirm that you want to install the Solver addin. For Excel 2003, I think you need SOLVER.XLA. Sorry, but I only have XL2007 available at the moment. The steps should be similar. Hope this helps, Hutch "Janet" wrote: Well, I assumed I could write a macro for a Solver transaction, but I keep getting for "SolverOK" in the macro: Compile Error; Sub or Function not defined. That probably means that a macro will not run the Solver for me, right?  Janet "L. Howard Kittle" wrote: Maybe something like this will get you started, where the value you want to insert in the cells is in F1 and the 400 cells are D1400 Sub ForHunRow() Dim i As Integer Dim j As Range, c As Range i = Range("F1").Value Set j = Range("D1400") For Each c In j c.Value = i Next End Sub HTH Regards, Howard "Janet" wrote in message ... I need to calculate a value to minimize a formula  I can do this with Solver. The problem: I have 400 rows for which I have to use the Solver calculation  and I need to update it occasionally. I'm trying to figure out how to do this automatically instead of solving each line manually. I suppose I could write a macro, but I don't know how to make the macro repeat itself until the last row and then stop. Any ideas? Thanks.  Janet . 
#6




Automate a Solver solution
The For...Next loop he gave you will process every cell in the range D1400
(in his example), one at a time. The cell being processed at any given point is referenced by the range variable "c". Inside the loop (between the For Each statement and the Next statement), you will update a value in a SolverOK command (presumably, based on the value in the cell referenced by the range variable "c".) Then you will call SolverSolve to actually run Solver. If you need to store the result somewhere, do it, then let the loop continue to the next cell. Your Solver commands might look something like this: SolverOk SetCell:="$E$1", MaxMinVal:=0, _ ValueOf:=c.Value, ByChange:="$D$1:$D$400" SolverSolve In my example above you are changing the ValueOf argument. Depending what you want to do you might change a different argument. Look at what you are changing when you run Solver manually for this process, andyou will see which argument you need to change programatically. Hope this helps, Hutch "Janet" wrote: Thanks!!! That did the trick, but now I don't know how to integrate the macro I just wrote into the program that Howard gave me so it will repeat itself for the relevant rows: Sub ForHunRow() Dim i As Integer Dim j As Range, c As Range i = Range("F1").Value Set j = Range("D1400") For Each c In j c.Value = i Next End Sub  Janet "Tom Hutchins" wrote: Before you can use Solver functions in VBA, you must establish a reference to the Solver addin. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select the Solver.xlam check box under Available References. If Solver.xlam does not appear under Available References, click Browse and open Solver.xlam in the \office12\library\Solver subfolder. If you have already installed the Solver addin in Excel, you will probably see it listed under Available References. To install Solver in Excel 2007: If the Solver button does not appear on the Data tab on the Ribbon, click the Microsoft Office Button, Excel Options, AddIns category, and then click the Go button. Then select the Solver AddIn check box, and click OK to install it. Click Yes to confirm that you want to install the Solver addin. For Excel 2003, I think you need SOLVER.XLA. Sorry, but I only have XL2007 available at the moment. The steps should be similar. Hope this helps, Hutch "Janet" wrote: Well, I assumed I could write a macro for a Solver transaction, but I keep getting for "SolverOK" in the macro: Compile Error; Sub or Function not defined. That probably means that a macro will not run the Solver for me, right?  Janet "L. Howard Kittle" wrote: Maybe something like this will get you started, where the value you want to insert in the cells is in F1 and the 400 cells are D1400 Sub ForHunRow() Dim i As Integer Dim j As Range, c As Range i = Range("F1").Value Set j = Range("D1400") For Each c In j c.Value = i Next End Sub HTH Regards, Howard "Janet" wrote in message ... I need to calculate a value to minimize a formula  I can do this with Solver. The problem: I have 400 rows for which I have to use the Solver calculation  and I need to update it occasionally. I'm trying to figure out how to do this automatically instead of solving each line manually. I suppose I could write a macro, but I don't know how to make the macro repeat itself until the last row and then stop. Any ideas? Thanks.  Janet . 
#7




Automate a Solver solution
On 5/23/2010 12:42 PM, Janet wrote:
Thanks!!! That did the trick, but now I don't know how to integrate the macro I just wrote into the program that Howard gave me so it will repeat itself for the relevant rows: Sub ForHunRow() Dim i As Integer Dim j As Range, c As Range i = Range("F1").Value Set j = Range("D1400") For Each c In j c.Value = i Next End Sub Hi. Here is a general outline. Here, I have a function in D1, and the changing cells are in A1:C1. The loop goes from Row 1 to 10. Hopefully, you can adjust it to your situation. Not quite what I use, but it should give you some ideas. In a loop, I find it best to just Reset everything. Sub Demo() '//Dana DeLouis Dim R As Long Dim Target Dim ChgCells SolverOptions AssumeLinear:=True SolverOptions AssumeNonNeg:=True For R = 1 To 10 'Row 1 to 10 SolverReset Target = Cells(R, 4).Address ChgCells = Cells(R, 1).Resize(1, 3).Address SolverOk SetCell:=Target, MaxMinVal:=2, ByChange:=ChgCells SolverAdd CellRef:=ChgCells, Relation:=3, FormulaText:="1" SolverAdd CellRef:=ChgCells, Relation:=1, FormulaText:="10" SolverSolve True Next R End Sub = = = = = = = HTH :) Dana DeLouis 
#8




Automate a Solver solution
Thanks!!!!!!!!!!!!!!!!!
 Janet "Tom Hutchins" wrote: The For...Next loop he gave you will process every cell in the range D1400 (in his example), one at a time. The cell being processed at any given point is referenced by the range variable "c". Inside the loop (between the For Each statement and the Next statement), you will update a value in a SolverOK command (presumably, based on the value in the cell referenced by the range variable "c".) Then you will call SolverSolve to actually run Solver. If you need to store the result somewhere, do it, then let the loop continue to the next cell. Your Solver commands might look something like this: SolverOk SetCell:="$E$1", MaxMinVal:=0, _ ValueOf:=c.Value, ByChange:="$D$1:$D$400" SolverSolve In my example above you are changing the ValueOf argument. Depending what you want to do you might change a different argument. Look at what you are changing when you run Solver manually for this process, andyou will see which argument you need to change programatically. Hope this helps, Hutch "Janet" wrote: Thanks!!! That did the trick, but now I don't know how to integrate the macro I just wrote into the program that Howard gave me so it will repeat itself for the relevant rows: Sub ForHunRow() Dim i As Integer Dim j As Range, c As Range i = Range("F1").Value Set j = Range("D1400") For Each c In j c.Value = i Next End Sub  Janet "Tom Hutchins" wrote: Before you can use Solver functions in VBA, you must establish a reference to the Solver addin. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select the Solver.xlam check box under Available References. If Solver.xlam does not appear under Available References, click Browse and open Solver.xlam in the \office12\library\Solver subfolder. If you have already installed the Solver addin in Excel, you will probably see it listed under Available References. To install Solver in Excel 2007: If the Solver button does not appear on the Data tab on the Ribbon, click the Microsoft Office Button, Excel Options, AddIns category, and then click the Go button. Then select the Solver AddIn check box, and click OK to install it. Click Yes to confirm that you want to install the Solver addin. For Excel 2003, I think you need SOLVER.XLA. Sorry, but I only have XL2007 available at the moment. The steps should be similar. Hope this helps, Hutch "Janet" wrote: Well, I assumed I could write a macro for a Solver transaction, but I keep getting for "SolverOK" in the macro: Compile Error; Sub or Function not defined. That probably means that a macro will not run the Solver for me, right?  Janet "L. Howard Kittle" wrote: Maybe something like this will get you started, where the value you want to insert in the cells is in F1 and the 400 cells are D1400 Sub ForHunRow() Dim i As Integer Dim j As Range, c As Range i = Range("F1").Value Set j = Range("D1400") For Each c In j c.Value = i Next End Sub HTH Regards, Howard "Janet" wrote in message ... I need to calculate a value to minimize a formula  I can do this with Solver. The problem: I have 400 rows for which I have to use the Solver calculation  and I need to update it occasionally. I'm trying to figure out how to do this automatically instead of solving each line manually. I suppose I could write a macro, but I don't know how to make the macro repeat itself until the last row and then stop. Any ideas? Thanks.  Janet . 
#9




Automate a Solver solution
Thanks!!!!! Very helpful.
 Janet "Dana DeLouis" wrote: On 5/23/2010 12:42 PM, Janet wrote: Thanks!!! That did the trick, but now I don't know how to integrate the macro I just wrote into the program that Howard gave me so it will repeat itself for the relevant rows: Sub ForHunRow() Dim i As Integer Dim j As Range, c As Range i = Range("F1").Value Set j = Range("D1400") For Each c In j c.Value = i Next End Sub Hi. Here is a general outline. Here, I have a function in D1, and the changing cells are in A1:C1. The loop goes from Row 1 to 10. Hopefully, you can adjust it to your situation. Not quite what I use, but it should give you some ideas. In a loop, I find it best to just Reset everything. Sub Demo() '//Dana DeLouis Dim R As Long Dim Target Dim ChgCells SolverOptions AssumeLinear:=True SolverOptions AssumeNonNeg:=True For R = 1 To 10 'Row 1 to 10 SolverReset Target = Cells(R, 4).Address ChgCells = Cells(R, 1).Resize(1, 3).Address SolverOk SetCell:=Target, MaxMinVal:=2, ByChange:=ChgCells SolverAdd CellRef:=ChgCells, Relation:=3, FormulaText:="1" SolverAdd CellRef:=ChgCells, Relation:=1, FormulaText:="10" SolverSolve True Next R End Sub = = = = = = = HTH :) Dana DeLouis . 
#10




On 5/23/2010 12:42 PM, Janet wrote:Hi. Here is a general outline.
Sub IPSOS() '//Dana DeLouis Dim R As Long Dim Target Dim ChgCells For R = 1 To 10 'Row 1 to 10 SolverReset SolverOptions Precision:=0.00001 Target = Cells(R, 6).Address ChgCells = Cells(R, 1).Address SolverOk SetCell:=Target, MaxMinVal:="3", ValueOf:="0", ByChange:=ChgCells SolverSolve True Next R End Sub On Saturday, May 22, 2010 12:37 PM Janet wrote: I need to calculate a value to minimize a formula  I can do this with Solver. The problem: I have 400 rows for which I have to use the Solver calculation  and I need to update it occasionally. I am trying to figure out how to do this automatically instead of solving each line manually. I suppose I could write a macro, but I do not know how to make the macro repeat itself until the last row and then stop. Any ideas? Thanks.  Janet On Saturday, May 22, 2010 8:29 PM L. Howard Kittle wrote: Maybe something like this will get you started, where the value you want to insert in the cells is in F1 and the 400 cells are D1400 Sub ForHunRow() Dim i As Integer Dim j As Range, c As Range i = Range("F1").Value Set j = Range("D1400") For Each c In j c.Value = i Next End Sub HTH Regards, Howard On Sunday, May 23, 2010 7:46 AM Janet wrote: Well, I assumed I could write a macro for a Solver transaction, but I keep getting for "SolverOK" in the macro: Compile Error; Sub or Function not defined. That probably means that a macro will not run the Solver for me, right?  Janet "L. Howard Kittle" wrote: On Sunday, May 23, 2010 8:19 AM Tom Hutchins wrote: Before you can use Solver functions in VBA, you must establish a reference to the Solver addin. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select the Solver.xlam check box under Available References. If Solver.xlam does not appear under Available References, click Browse and open Solver.xlam in the \office12\library\Solver subfolder. If you have already installed the Solver addin in Excel, you will probably see it listed under Available References. To install Solver in Excel 2007: If the Solver button does not appear on the Data tab on the Ribbon, click the Microsoft Office Button, Excel Options, AddIns category, and then click the Go button. Then select the Solver AddIn check box, and click OK to install it. Click Yes to confirm that you want to install the Solver addin. For Excel 2003, I think you need SOLVER.XLA. Sorry, but I only have XL2007 available at the moment. The steps should be similar. Hope this helps, Hutch "Janet" wrote: On Sunday, May 23, 2010 12:42 PM Janet wrote: Thanks!!! That did the trick, but now I do not know how to integrate the macro I just wrote into the program that Howard gave me so it will repeat itself for the relevant rows: Sub ForHunRow() Dim i As Integer Dim j As Range, c As Range i = Range("F1").Value Set j = Range("D1400") For Each c In j c.Value = i Next End Sub  Janet "Tom Hutchins" wrote: On Sunday, May 23, 2010 10:34 PM Tom Hutchins wrote: The For...Next loop he gave you will process every cell in the range D1400 (in his example), one at a time. The cell being processed at any given point is referenced by the range variable "c". Inside the loop (between the For Each statement and the Next statement), you will update a value in a SolverOK command (presumably, based on the value in the cell referenced by the range variable "c".) Then you will call SolverSolve to actually run Solver. If you need to store the result somewhere, do it, then let the loop continue to the next cell. Your Solver commands might look something like this: SolverOk SetCell:="$E$1", MaxMinVal:=0, _ ValueOf:=c.Value, ByChange:="$D$1:$D$400" SolverSolve In my example above you are changing the ValueOf argument. Depending what you want to do you might change a different argument. Look at what you are changing when you run Solver manually for this process, andyou will see which argument you need to change programatically. Hope this helps, Hutch "Janet" wrote: On Monday, May 24, 2010 6:18 PM Dana DeLouis wrote: On 5/23/2010 12:42 PM, Janet wrote: Hi. Here is a general outline. Here, I have a function in D1, and the changing cells are in A1:C1. The loop goes from Row 1 to 10. Hopefully, you can adjust it to your situation. Not quite what I use, but it should give you some ideas. In a loop, I find it best to just Reset everything. Sub Demo() '//Dana DeLouis Dim R As Long Dim Target Dim ChgCells SolverOptions AssumeLinear:=True SolverOptions AssumeNonNeg:=True For R = 1 To 10 'Row 1 to 10 SolverReset Target = Cells(R, 4).Address ChgCells = Cells(R, 1).Resize(1, 3).Address SolverOk SetCell:=Target, MaxMinVal:=2, ByChange:=ChgCells SolverAdd CellRef:=ChgCells, Relation:=3, FormulaText:="1" SolverAdd CellRef:=ChgCells, Relation:=1, FormulaText:="10" SolverSolve True Next R End Sub = = = = = = = HTH :) Dana DeLouis On Tuesday, May 25, 2010 10:36 AM Janet wrote: Thanks!!!!!!!!!!!!!!!!!  Janet "Tom Hutchins" wrote: On Tuesday, May 25, 2010 10:40 AM Janet wrote: Thanks!!!!! Very helpful.  Janet "Dana DeLouis" wrote: On Saturday, February 26, 2011 5:33 PM Mat wrote: Hi, I need to do something very similar to this but, I need my target cells to be N40:N70 target value is 0 By changing cells are D4070 I may need to extend the target and by changing cells to N100 and D100 (respectively)...is this easy to do? Thanks, Mat On Wednesday, June 22, 2011 1:28 PM Andy Pappas wrote: Hello I am trying to solve a trial and error equation with the solver. I have seen the code above and tried to solve my problem. I have reached to the following code: Sub Height() Dim R As Long Dim Target Dim ChgCells For R = 1 To 10 'Row 1 to 10 SolverReset SolverOptions Precision:=0.00001 Target = Cells(R, 6).Address ChgCells = Cells(R, 1).Address SolverOk SetCell:=Target, MaxMinVal:="3", ValueOf:="0", ByChange:=ChgCells SolverSolve True Next R End Sub I have put trial and error equations to cells F1:F10 and i want to set the equation to zero by giving values to the cells A1:A10. I made it work but : 1. When i change the chgcells reference from cells(R,1) to cells(R,2) so that the values to be placed to the second column it returns the value 0 from B1:B10 (i can;t change the column it opnly works for A column. 2.I can't set the restrictions for e.x. 1< (each A value) < 1 3. For negative values it doesn't work 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Validating solver solution  Excel Discussion (Misc queries)  
How do I save a solver solution in VBA  Excel Discussion (Misc queries)  
Using Solver  integer solution  Excel Discussion (Misc queries)  
Solver solution hessian  Excel Discussion (Misc queries)  
Solver does not find correct solution???  Excel Discussion (Misc queries) 