There are some examples he
http://support.microsoft.com/default.aspx/kb/843304
http://peltiertech.com/Excel/SolverVBA.html

If that doesn't give you any ideas, try to record a macro before you launch
solver. Then, run through the steps in solver, setting the constraints to
your named ranges. Turn off the macro recordrd and view the code.
Everything should be in there.

Good luck,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Zoheb" wrote:

Hi

I am having trouble naming ranges in an excel worksheet using the following:

Dim ReturnString As String
Dim noStocks As Integer

noStocks = 9

ReturnString = "=Sheet7!R2C2:R2C" & noStocks
ThisWorkbook.Names.Add name:="Exptdret", RefersTo:=ReturnString

The sheet that I am trying to name this range is on has been named using a
class module:

Dim opti as Worksheet

Set opti = NewSheet.createTempWorksheet(ActiveWorkbook, "Optimisation")

where the function NewSheet has been created in a module where everytime my
program runs it clears the worksheet so new data is able to be produced.

The range name appears on Name Manager, however the cell values are listed
as Ref#

I hope this is clear for someone to help me with.

If not then I am able to send the file which would make the problem a lot
clearer.

Thanks

Zoheb