Solver: Using Same Parameters for Multiple Rows

eds10

New Member
Joined
Sep 20, 2004
Messages
9
All, any help would be greatly appreciated.

I'm trying to find a shortcut to using solver rather than having to do it separately for every row in a column since "Set Target Cell Must Be a Single Cell on the Active Sheet." Is there a way to run Solver for multiple rows within a column rather than having to do it individually?

Let me elaborate:
I have the same formula is cells B6:400. I have hard coded number for which I would like to use Solver in C6:400 such that the number in C is "solved" so B=1.

For example:

Target Cell: B6
Value of: 1
By Changing: C6

For rows 6:400.

Rather than going to Tools, Solver, etc each time, is there a way to get around the "Single Cell" rule or any solutions that you can think of.

Thanks in advance for your help.

ES
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984
How about this? It uses Goal Seek instead of Solver because you have only one "by changing" cell...

Code:
Sub My_Goal_Seek_Loop()
Dim i As Long
For i = 6 To 400
    Range("B" & i).GoalSeek Goal:=1, ChangingCell:=Range("C" & i)
Next i
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,487
Messages
5,529,156
Members
409,851
Latest member
Ingar
Top