Hi everybody, im pretty new to VBA programming, but i couldn't find solution to my problem in any online tutourial. Also,
english is not my native language, so forgive me any mistakes.
This is what I wrote:
Dim i As Integer
For i = 1 To 50
Names.Add "function", RefersTo:=Range("o11").Offset(i - 1)
Names.Add "variables", RefersTo:=Range("b11:b60").Offset(i - 1)
Names.Add "constraint", RefersTo:=Range("i11:i60").Offset(i - 1)
Range("r10").Value = i
SolverOk SetCell:="function", MaxMinVal:=1, ByChange:=Range("variables")
SolverAdd CellRef:=Range("constraint"), Relation:=2, FormulaText:="1600"
SolverAdd CellRef:=Range("variables"), Relation:=3, FormulaText:="0"
Range("r11").Offset(i - 1).Value = Range("o11").Offset(i - 1).Value
Basicly my intention was to write a macro that uses Solver in a loop, but in every iteration changes Target Cell and
"shrinks" range of cells in ByChange and in constraints - in first iteration it should maximise O11 for constraints
b11:b60 <= 0 & i11:i60 = 1600, in a second iteration maximise O12 for constraints b12:b60 <= 0 & i12:i60 = 1600
etc. for 50 iterations. I wanted to add .Resize(-1,0), after every Offset(i - 1), but than Excel messages me with error, but
thats the minor deal.
Major problem is, that solver doesen't work in that loop, I mean when I use it
"manually" it can find result for first iteraion, but when I run the macro it just leaves ByChange cells
(if you're curious - it should simulate time-inconsistent consumer with "myopia", that choses
some consumption path in period i=1 and than revaluates his discounted utility in each of next periods and constantly changes
I use Excel 2007.