Regarding excel and VBA

C

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
 
R

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]...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top