Looping Solver VBA Question

[Gleny]

Greetings!
Im trying to run a solver loop that will loop through and optimize for the highest return possible on a yearly basis.
See image. What I want to do is use the previous years returns across the 7 asset classes shown to optimize the weights invested the following year in the hopes that this will provide favorable data to just simple equal weighting year after year.

The core of what I want to loop is below.
I4 is the starting value id like to optimize and then Id like to take the value in I5 and copy and paste the value alone into I5
I'd like the loop to then go to I5 and optimize based on the same constraints below and then copy and the values of I6 into I6...and so on until the referenced cell contains no value.

I cant quite figure out how to get the loop to work so im simply posting the optimizer code I know will work...

Sub Solver_Final()
'
' Solver_Final Macro
'
' Keyboard Shortcut: Ctrl+Shift+O
'
        SolverReset
        SolverAdd CellRef:="$I$1", Relation:=2, FormulaText:="1"
        SolverAdd CellRef:="$B$1:$H$1", Relation:=3, FormulaText:=".1"
        SolverOk SetCell:="[COLOR=rgb(97, 189, 109)][B]I4[/B][/COLOR]", MaxMinVal:=1, ValueOf:=0, ByChange:="$B$1:$H$1", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve True
End Sub

Let me know if further clarification is needed.

 

[sheetspread]

Why do you need solver? It looks like the weights are just 1 divided by 7.

 

[Gleny]

Why do you need solver? It looks like the weights are just 1 divided by 7.

That's just the values that are there currently, I will be changing those values using the constraints shown in my code to maximize the value in column "I" for each row.

 

[sheetspread]

Can you post the image with XL2BB so I can test?

 

[Gleny]

Final.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Weights	0.142857143	0.142857143	0.142857143	0.142857143	0.142857143	0.142857143	0.142857143	1	$$$	$ 5,000.00
2	Asset Class	US Large Stock	US Small Stock	Non-US Stock	US Bonds	US Cash	Real Estate	Commodities		Inflation
3	Index	S&P 500	Russell 2000 (1979-xx) Ibbotson Small Stock (1970-78)	MSCI EAFE NR USD	BarCap Aggregate Bond Index (1976-xx) Ibbotson Int-term Govt Bond (1970-75)	USTREAS Stat US T-Bill 90 Day TR	DJ US Select REIT Index (1978-xx) NAREIT Equity REIT Index 1972-77, 1970-71 from Chan	Commodities (Goldman Sachs Commodity Index)	Portfolio	Consumer Price Index	Prediction
4	1970	3.94	(17.43)	(11.66)	16.86	6.58	17.55	15.10	4.42	5.57	$ 5,000.00
5	1971	14.30	16.50	29.59	8.72	4.42	17.41	21.08	16.00	3.27	$ 10,800.13
6	1972	18.99	4.43	36.35	5.16	4.15	8.01	42.43	17.07	3.41	$ 17,644.22
7	1973	(14.69)	(30.90)	(14.92)	4.61	7.26	(15.52)	74.96	1.54	8.71	$ 22,916.38
8	1974	(26.47)	(19.95)	(23.16)	5.69	8.12	(21.40)	39.51	(5.38)	12.34	$ 26,683.46
9	1975	37.23	52.82	35.39	7.83	5.93	19.30	(17.22)	20.18	6.94	$ 37,068.89
10	1976	23.93	57.38	2.54	15.60	5.09	47.59	(11.92)	20.03	4.86	$ 49,493.34
11	1977	(7.16)	25.38	18.06	3.04	5.40	22.42	10.37	11.07	6.70	$ 59,973.22
12	1978	6.57	23.46	32.62	1.39	7.43	10.98	31.61	16.29	9.02	$ 74,745.64
13	1979	18.61	43.07	4.75	1.93	10.55	48.99	33.81	23.10	13.29	$ 97,012.80
14	1980	32.50	38.60	22.58	2.71	12.05	33.12	11.08	21.81	12.52	$ 123,166.52
15	1981	(4.92)	2.03	(2.28)	6.25	14.96	17.88	(23.01)	1.56	8.92	$ 130,086.02
16	1982	21.55	24.95	(1.86)	32.62	11.07	20.91	11.56	17.26	3.83	$ 157,535.59
17	1983	22.56	29.13	23.69	8.36	8.94	32.17	16.26	20.16	3.79	$ 194,291.76
18	1984	6.27	(7.30)	7.38	15.15	9.90	21.89	1.05	7.76	3.95	$ 214,372.73
19	1985	31.73	31.05	56.16	22.10	7.73	6.50	10.01	23.61	3.80	$ 269,989.59
20	1986	18.67	5.68	69.44	15.26	6.15	19.75	2.04	19.57	1.10	$ 327,827.37
21	1987	5.25	(8.80)	24.63	2.76	5.96	(6.59)	23.77	6.71	4.43	$ 354,829.46
22	1988	16.61	25.02	28.27	7.89	6.88	17.48	27.93	18.58	4.42	$ 425,766.29
23	1989	31.69	16.26	10.54	14.53	8.39	2.72	38.28	17.49	4.65	$ 505,217.27
24	1990	(3.10)	(19.48)	(23.45)	8.96	7.74	(23.44)	29.08	(3.38)	6.11	$ 493,118.38
25	1991	30.47	46.04	12.13	16.00	5.54	23.84	(6.13)	18.27	3.06	$ 588,202.84
26	1992	7.62	18.41	(12.17)	7.40	3.52	15.13	4.42	6.33	2.90	$ 630,453.54
27	1993	10.08	18.88	32.56	9.75	3.07	15.14	(12.33)	11.02	2.75	$ 704,931.16
28	1994	1.32	(1.82)	7.78	(2.92)	4.36	2.66	5.29	2.38	2.67	$ 726,719.10
29	1995	37.58	28.45	11.21	18.47	5.66	12.24	20.33	19.13	2.54	$ 870,770.43
30	1996	22.96	16.49	6.05	3.63	5.14	37.05	33.92	17.89	3.32	$ 1,031,572.86
31	1997	33.36	22.36	1.78	9.65	5.20	19.66	(14.07)	11.13	1.70	$ 1,151,434.69
32	1998	28.58	(2.55)	20.00	8.69	4.91	(17.01)	(35.75)	0.98	1.61	$ 1,167,735.60
33	1999	21.04	21.26	26.96	(0.82)	4.78	(2.58)	40.92	15.94	2.68	$ 1,358,831.59
34	2000	(9.10)	(3.02)	(14.17)	11.63	5.98	31.04	49.74	10.30	3.39	$ 1,503,788.99
35	2001	(11.89)	2.49	(21.44)	8.44	3.34	12.35	(31.93)	(5.52)	1.55	$ 1,425,777.11
36	2002	(22.10)	(20.48)	(15.94)	10.25	1.63	3.58	32.07	(1.57)	2.38	$ 1,408,394.90
37	2003	28.68	47.25	38.59	4.10	1.03	36.18	20.72	25.22	1.88	$ 1,768,631.00
38	2004	10.88	18.33	20.25	4.34	1.44	33.16	17.28	15.10	3.26	$ 2,040,643.94
39	2005	4.91	4.55	13.54	2.43	3.25	13.82	25.55	9.72	3.42	$ 2,244,030.07
40	2006	15.79	18.37	26.34	4.33	4.85	35.97	(15.09)	12.94	2.54	$ 2,539,360.61
41	2007	5.49	(1.57)	11.17	6.97	4.44	(17.55)	32.67	5.95	4.08	$ 2,695,361.28
42	2008	(37.00)	(33.79)	(43.38)	5.24	1.39	(39.20)	(46.49)	(27.60)	0.09	$ 1,956,355.29
43	2009	26.46	27.17	31.78	5.93	0.16	28.46	13.48	19.06	2.72	$ 2,334,297.76
44	2010	15.06	26.85	7.75	6.54	0.15	28.07	9.03	13.35	1.50	$ 2,650,942.44
45	2011	2.11	(4.18)	(12.14)	7.84	0.06	9.37	(1.18)	0.27	2.96	$ 2,663,086.31
46	2012	16.00	16.35	17.32	4.21	0.08	17.12	0.08	10.17	1.74	$ 2,938,834.94
47	2013	32.39	38.82	22.78	(2.02)	0.06	1.22	(1.22)	13.15	1.50	$ 3,330,170.81
48	2014	13.69	4.89	(4.90)	5.97	0.03	32.00	(33.06)	2.66	0.76	$ 3,423,740.30
49	2015	1.38	(4.41)	(0.81)	0.55	0.05	4.48	(32.86)	(4.52)	0.73	$ 3,274,091.31
50	2016	11.96	21.31	1.00	2.65	0.32	6.68	11.37	7.90	2.07	$ 3,537,628.09
51	2017	21.83	14.65	25.03	3.54	0.93	3.76	5.77	10.79	2.11	$ 3,924,265.91
52	2018	(4.38)	(11.01)	(13.79)	0.01	1.94	(4.22)	(13.82)	(6.47)	1.91	$ 3,675,478.02
53	2019	31.49	25.52	22.01	8.72	2.06	23.1	17.63	18.65	2.29	$ 4,365,849.66
EVERYYEAR

Cell Formulas
Range	Formula
B1:H1	B1	=1/7
I1	I1	=SUM(B1:H1)
I6:I53	I6	=SUMPRODUCT($B$1:$H$1,B6:H6)
K6:K53	K6	=(((I6/100)+1)*K5)+$K$1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I1	Cell Value	<=1	text	NO
I1	Cell Value	>1	text	NO

 

[Gleny]

Can you post the image with XL2BB so I can test?

Just posted.

 

[sheetspread]

What constraints determine the weights?

 

[Gleny]

What constraints determine the weights?

So...
Im maximizing the I4 Value
while constraining that each of the weight values is at least .1
"I1" needs to equal 1 exactly

 

[sheetspread]

Sub Solver_Final()
Dim lr%, i%
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To lr
        SolverReset
        SolverAdd CellRef:="$I$1", Relation:=2, FormulaText:="1"
        SolverAdd CellRef:="$B$1:$H$1", Relation:=3, FormulaText:=".1"
        SolverOk SetCell:="I" & i, MaxMinVal:=1, ValueOf:=0, ByChange:="$B$1:$H$1", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve True
        Cells(i, 9).Value = Cells(i, 9).Value
        Next
End Sub

loops what you have, but to just count the max as .4 and every other as .1 then

=SUM(IF(B4:H4=MAX(B4:H4),0.4,0.1)*B4:H4)

ctrl-shift-enter

in I4 copied down works without solver.

 

[Gleny]

Sub Solver_Final()
Dim lr%, i%
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To lr
        SolverReset
        SolverAdd CellRef:="$I$1", Relation:=2, FormulaText:="1"
        SolverAdd CellRef:="$B$1:$H$1", Relation:=3, FormulaText:=".1"
        SolverOk SetCell:="I" & i, MaxMinVal:=1, ValueOf:=0, ByChange:="$B$1:$H$1", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve True
        Cells(i, 9).Value = Cells(i, 9).Value
        Next
End Sub

loops what you have, but to just count the max as .4 and every other as .1 then

=SUM(IF(B4:H4=MAX(B4:H4),0.4,0.1)*B4:H4)

ctrl-shift-enter

in I4 copied down works without solver.

this gives me a sub not defined error.