Hi, I m a noob using VBA currently. I have this excel sheet which requires me to use solver together with for loop. I have some problems declaring some variables as follow:

For i = 40 To 40 Step 1

valueForSetCell = "$AT" & i
valueForSetCell2 = "$AS" & i
valueForByChange = "$AK" & i & "," & "$AI" & i & "," & "$AG" & i
valueForByChange2 = "$AK" & i & "," & "$AI" & i & "," & "$AH" & i & "," & "$AG" & i
valueForByChange3 = "$AH" & i & "," & "$AG" & i
constraint1 = "$CB" & i
constraint2 = "$CC" & i
constraint3 = "$AI" & i
constraint4 = "$AK" & i
constraint5 = "$AK" & "i-1"

SolverOk SetCell:=valueForSetCell, MaxMinVal:=3, ValueOf:="0.001", ByChange:=valueForByChange2
SolverAdd CellRef:=constraint1, Relation:=1, FormulaText:="0.001"
SolverAdd CellRef:=constraint2, Relation:=1, FormulaText:="0.001"
SolverAdd CellRef:=valueForSetCell2, Relation:=1, FormulaText:="0.001"
SolverAdd CellRef:=constraint3, Relation:=1, FormulaText:="0.95"
SolverAdd CellRef:=constraint4, Relation:=1, FormulaText:=constraint5
SolverOk SetCell:=valueForSetCell, MaxMinVal:=3, ValueOf:="0.001", ByChange:=valueForByChange2
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1

Next i

End Sub

the file above has a problem lie with the constraint 4 and 5, which i would like constraint 5 to be the cell from the previous row. So in other words, i would want for row 40, the value in AK40 < AK39, pls help! I do not know how to make it to AK39 as i proceed to row 40 in my loop thanks




Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET NOAA Weather WebService Server Control
http://www.eggheadcafe.com/tutorials...ther-webs.aspx