Automate a Solver solution
in [Functions]
|
From: Tom Hutchins on 23 May 2010 22:34 The For...Next loop he gave you will process every cell in the range D1:D400 (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("D1:D400") > > 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 add-in. 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 add-in 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, Add-Ins category, and then click > > the Go button. Then select the Solver Add-In check box, and click OK to > > install it. Click Yes to confirm that you want to install the Solver add-in. > > > > 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 D1:D400 > > > > > > > > > > > > Sub ForHunRow() > > > > Dim i As Integer > > > > Dim j As Range, c As Range > > > > > > > > i = Range("F1").Value > > > > Set j = Range("D1:D400") > > > > > > > > For Each c In j > > > > c.Value = i > > > > Next > > > > > > > > End Sub > > > > > > > > HTH > > > > Regards, > > > > Howard > > > > > > > > "Janet" <Janet(a)discussions.microsoft.com> wrote in message > > > > news:84F9918D-E862-43EA-A2C5-E6488CE71F34(a)microsoft.com... > > > > >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 > > > > > > > > > > > > . > > > >
From: Dana DeLouis on 24 May 2010 18:18 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("D1:D400") > > 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
From: Janet on 25 May 2010 10:36 Thanks!!!!!!!!!!!!!!!!! -- Janet "Tom Hutchins" wrote: > The For...Next loop he gave you will process every cell in the range D1:D400 > (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("D1:D400") > > > > 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 add-in. 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 add-in 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, Add-Ins category, and then click > > > the Go button. Then select the Solver Add-In check box, and click OK to > > > install it. Click Yes to confirm that you want to install the Solver add-in. > > > > > > 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 D1:D400 > > > > > > > > > > > > > > > Sub ForHunRow() > > > > > Dim i As Integer > > > > > Dim j As Range, c As Range > > > > > > > > > > i = Range("F1").Value > > > > > Set j = Range("D1:D400") > > > > > > > > > > For Each c In j > > > > > c.Value = i > > > > > Next > > > > > > > > > > End Sub > > > > > > > > > > HTH > > > > > Regards, > > > > > Howard > > > > > > > > > > "Janet" <Janet(a)discussions.microsoft.com> wrote in message > > > > > news:84F9918D-E862-43EA-A2C5-E6488CE71F34(a)microsoft.com... > > > > > >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 > > > > > > > > > > > > > > > . > > > > >
From: Janet on 25 May 2010 10:40 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("D1:D400") > > > > 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 > . >
First
|
Prev
|
Pages: 1 2 Prev: Ranking - Not Sorting Properly. Next: Can I Use a Count Function for Text? |