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

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

Hi. See if this helps any:

noStocks = 9
ThisWorkbook.Names.Add _
"Exptdret", _
Worksheets("Sheet7").Range("C2").Resize(noStocks - 1)


Additional thoughts:
Make sure "ThisWorkbook" from above is pointing to your Solver Workbook.

If you recorded a macro and got "...Sheet7!R2C2:R2C" , then make sure
your Workbook is switched to A1 notation (not R1C1 notation). Solver,
from Excel 97 on, requires A1 notation.

= = =
Dana DeLouis



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