variables in excel solver for vba

how can you use variables in excel solver? i don't want the changing
cell always to be "R1C1" i want it to be something that will change.
this is what i have so far:

SolverReset

SolverOk SetCell:="R & CStr(numProducts + 3) & C2", MaxMinVal:=2,
ValueOf:="0", _
ByChange:="R & CStr() & C & CStr() & :R & CStr() & C & CStr()"

SolverAdd CellRef:= _
"R2 & C & CStr(numSites + 3) & :R & CStr(numProducts + 1) & C &
CStr(numSites + 3)", Relation:=2, FormulaText:="1" 'every product must
be bought
SolverAdd CellRef:= _
"R & CStr(numProducts + 2) & C2:R & CStr(numProducts + 2) & C &
CStr(numSites + 1)", Relation:=5, FormulaText:="binary" 'binary
constraint visit or not
SolverAdd CellRef:= _
"R2 & C & CStr(numSites + 2) & :R & CStr(numProducts + 1) & C &
CStr(numSites + 2)", Relation:=3, FormulaText:="1"

SolverSolve UserFinish:=False
SolverFinish KeepFinal:=1

Can i use concatenation like that? am i just writing it wrong? vba
runs my sub without any errors so i'm really confused...

any ideas would be greatly appreciated...thanks!

Hi. I don't quite follow, so here's something very general.
I find it very hard to work w/ strings in this fashion, as you've
discovered.
You may have better luck with the following format..
Range(Cells(r,c), Cells(r2,c2))
Where your r's & c's can be your variables.
As a side benefit, Excel versions since 97 work with the A1 notation, and
will not work with R1C1 formatting.

Here's a very basic idea...

'// Example:
numProducts = 5
numSites = 7

SolverOk Cells(numProducts + 3, 2), 2, , Range(Cells(numProducts + 4, 2),
Cells(numProducts + 20, 2))

SolverAdd Range(Cells(2, numSites + 3), Cells(numProducts + 1, 2)), 2, 1

If you wish to work with String addresses instead, you can do something like
this

Dim ThisAddress
ThisAddress = Range(Cells(2, numSites + 3), Cells(numProducts + 1,
2)).Address

Then add the string 'ThisAddress' to your Solver program.
--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


<[email protected]> wrote in message
news:[email protected]...
> how can you use variables in excel solver? i don't want the changing
> cell always to be "R1C1" i want it to be something that will change.
> this is what i have so far:
>
> SolverReset
>
> SolverOk SetCell:="R & CStr(numProducts + 3) & C2", MaxMinVal:=2,
> ValueOf:="0", _
> ByChange:="R & CStr() & C & CStr() & :R & CStr() & C & CStr()"
>
> SolverAdd CellRef:= _
> "R2 & C & CStr(numSites + 3) & :R & CStr(numProducts + 1) & C &
> CStr(numSites + 3)", Relation:=2, FormulaText:="1" 'every product must
> be bought
> SolverAdd CellRef:= _
> "R & CStr(numProducts + 2) & C2:R & CStr(numProducts + 2) & C &
> CStr(numSites + 1)", Relation:=5, FormulaText:="binary" 'binary
> constraint visit or not
> SolverAdd CellRef:= _
> "R2 & C & CStr(numSites + 2) & :R & CStr(numProducts + 1) & C &
> CStr(numSites + 2)", Relation:=3, FormulaText:="1"
>
> SolverSolve UserFinish:=False
> SolverFinish KeepFinal:=1
>
> Can i use concatenation like that? am i just writing it wrong? vba
> runs my sub without any errors so i'm really confused...
>
> any ideas would be greatly appreciated...thanks!
>