A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Automate a Solver solution



 
 
Thread Tools Display Modes
  #1  
Old May 22nd, 2010, 05:37 PM posted to microsoft.public.excel.worksheet.functions
Janet
external usenet poster
 
Posts: 211
Default 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  
Old May 23rd, 2010, 01:29 AM posted to microsoft.public.excel.worksheet.functions
L. Howard Kittle
external usenet poster
 
Posts: 516
Default 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  
Old May 23rd, 2010, 12:46 PM posted to microsoft.public.excel.worksheet.functions
Janet
external usenet poster
 
Posts: 211
Default 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  
Old May 23rd, 2010, 01:19 PM posted to microsoft.public.excel.worksheet.functions
Tom Hutchins
external usenet poster
 
Posts: 722
Default Automate a Solver solution

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 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  
Old May 23rd, 2010, 05:42 PM posted to microsoft.public.excel.worksheet.functions
Janet
external usenet poster
 
Posts: 211
Default 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 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 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  
Old May 24th, 2010, 03:34 AM posted to microsoft.public.excel.worksheet.functions
Tom Hutchins
external usenet poster
 
Posts: 722
Default 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 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 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  
Old May 24th, 2010, 11:18 PM posted to microsoft.public.excel.worksheet.functions
Dana DeLouis[_3_]
external usenet poster
 
Posts: 184
Default 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  
Old May 25th, 2010, 03:36 PM posted to microsoft.public.excel.worksheet.functions
Janet
external usenet poster
 
Posts: 211
Default 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 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 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  
Old May 25th, 2010, 03:40 PM posted to microsoft.public.excel.worksheet.functions
Janet
external usenet poster
 
Posts: 211
Default 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
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:06 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.