From: Janet on
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
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
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
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
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
> > >
> > >
> > > .
> > >