Naming Ranges to use for Solver

Z

Zoheb

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
 
D

Dana DeLouis

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
 

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