help on how to write a Macro on how to repeat solver

friendlyyyyy

unread,

Jun 24, 2002, 10:59:27 PM6/24/02

to

Iam trying to write a macro that will solve one cell by changing
another cell then offset the Range to a new cell and solve that cell
while changing a new cell. For example:
solve cell(a1) by changing cell(b1) then solve cell(a2) by changing
cell(b2). Thank you

Vasant Nanavati

unread,

Jun 24, 2002, 11:32:25 PM6/24/02

to

Hi:

I'm always amazed at the number of mind-readers we have in this
newsgroup, but I'd be surprised if anyone can figure out what you are
trying to accomplish (I'm often surprised, though).

Could you be (much) more specific, perhaps with an example?
--
Regards,

Vasant.

**No direct emails please--keep discussion in newsgroup.**

"friendlyyyyy" <ema...@msn.com> wrote in message
news:77c90ec1.02062...@posting.google.com...

Tom Ogilvy

unread,

Jun 24, 2002, 11:47:03 PM6/24/02

to

He wants so use Solver to solve one situation, then use solver to solve a
second situation using a macro.

This might help:

Creating Visual Basic Macros that Use Microsoft Excel Solver
http://support.microsoft.com/support/Excel/Content/Solver/SOLVER.asp

Regards,
Tom Ogilvy

Vasant Nanavati <vas...@aol.com> wrote in message
news:OqgTRj$GCHA.2472@tkmsftngp11...

Dana DeLouis

unread,

Jun 25, 2002, 1:12:37 AM6/25/02

to

Tom gave you an excellent reference. A common problem when using Solver in
a loop is to keep adding constraints, with the old constraints still there.
Consider using "Reset" at each loop if it is appropriate.

With just two cells, would GoalSeek work for you?

Range("A1").GoalSeek Goal:=100, ChangingCell:=Range("B1")

or in a loop...
Cells(j, 1).GoalSeek Goal:=100, ChangingCell:=Cells(j, 2)

--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =

"friendlyyyyy" <ema...@msn.com> wrote in message
news:77c90ec1.02062...@posting.google.com...

friendlyyyyy

unread,

Jun 25, 2002, 4:39:22 PM6/25/02

to

Thank you for lending a hand. The link was helpful but I am still
struggling. Let me see if I can clarify my point. This is the program
that I wrote and I get a run time error 424. I am trying to solve by
setting G6=0 and changing cell C14. when this is done I would like to
set cell G7=0 and solve by chaning cell C15.
Thank you

For J = 0 To 5
firstrange = Range("C14")
secondrange = Range("G6")
first = firstrange.Offset(J, 0)
ssecond = secondrange.Offset(0, J)
SolverOk SetCell:=ssecond, MaxMinVal:=3, ValueOf:=0,
ByChange:=first
SolverSolve
Next J

"Dana DeLouis" <ng_...@hotmail.com> wrote in message news:<#cZUFcAHCHA.1844@tkmsftngp11>...

Tushar Mehta

unread,

Jun 25, 2002, 5:35:36 PM6/25/02

to

It isn't clear what firstrange, secondrange, first, and ssecond are, but
try this *untested* code:

dim ChangeCell as range, TargetCell as range, j as integer

set changecell=activesheet.range("C14")
set targetcell=activesheet.range("G6")
for j=0 to 5
solverreset
solverok setcell:=targetcell.offset(j,0), _
MaxMinVal:=3, ValueOf:=0, _
bychange:=changecell.offset(j,0)
solversolve
next j

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Microsoft MVP -- Excel
--

In <77c90ec1.02062...@posting.google.com>, friendlyyyyy
<ema...@msn.com> wrote

Dana DeLouis

unread,

Jun 25, 2002, 6:57:54 PM6/25/02

to

> firstrange = Range("C14")
> secondrange = Range("G6")

Just to point this out, you are setting these to values, and not to a
"Range" object.
You will want to use "Set."
Also in a loop, you will most likely want to use:
SolverSolve UserFinish:=True

Just a little side note. My personal opinion is that "Frontline" has this
setting backwards.
"UserFinish:=True" looks like the user should be involved with interacting
with the standard Solver Results dialog box.
You, the user, do not want to finish it, so it appears to me that the
appropriate setting should be False.
Well, that's my 2 cents worth!

Something like...

For j = 0 To 5
Set firstrange = Range("C14").Offset(j, 0)
Set secondrange = Range("G6").Offset(0, j)

SolverOk _
SetCell:=secondrange, _
MaxMinVal:=3, _
ValueOf:=0, _
ByChange:=firstrange

SolverSolve UserFinish:=True
Next j

--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =

"friendlyyyyy" <ema...@msn.com> wrote in message
news:77c90ec1.02062...@posting.google.com...

friendlyyyyy

unread,

Jun 26, 2002, 6:29:30 PM6/26/02

to

Dana Tushar:
Thank you both. It works like a charm.
Emad

Tushar Mehta <ng_p...@bigfoot.com> wrote in message news:<MPG.1782ac2ef...@msnews.microsoft.com>...