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