Regarding excel and VBA

[Cheng Jianhua]

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/tutorial...dc-d4988c174263/aspnet-noaa-weather-webs.aspx

 

[Rick Rothstein]

Let's look at your first assignment...

valueForSetCell = "$AT" & i

The valueForSetCell does not contain the contents of the cell whose
address is $ATi (i being the number in the current iteration of the loop),
rather it just contains the characters $, A, T and whatever digits i
currently evaluates as. To get the actual value in the cell, you need to
pass the address string into the Range function and retrieve its Value
property. Try it this way (and do likewise with the rest of your
assignments) and see if that works for you...

valueForSetCell = Range("AT" & i)

--
Rick (MVP - Excel)


in message
news:[email protected]...