Automate a Solver solution
in [Functions]
|
From: Janet on 22 May 2010 12:37 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: L. Howard Kittle on 22 May 2010 20:29 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 23 May 2010 07:46 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: Tom Hutchins on 23 May 2010 08:19 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 23 May 2010 12:42 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 > > > > > > > > > . > > >
|
Next
|
Last
Pages: 1 2 Prev: Ranking - Not Sorting Properly. Next: Can I Use a Count Function for Text? |