Creating named ranges.....Error

[shishi]

Hi all,

I have written a Sub to create a named range in a sheet. The
arguments to Sub are name and sheet name. The code is shown below. It
creates a named cell. For example if I call the Sub as below,

Call GenerateNames("NAME1", Sheet1) when the cell A12 is active, it
creates a named cell but it is not working the way it is supposed to
be. Because the follwing will not work
Set mCell = ThisWorkbook.Sheets("Sheet1").Range("NAME1").Offset(1, 0).
I find that the GenerateNames("NAME1", Sheet1) is generating named
range like A12 = NAME1. How can I modify the Sub to create a named
range properly?

Sub GenerateNames(Fname As String, Shname As String)
Add = ActiveCell.Address
nAdd = Shname & "!" & Add
ActiveCell.Value = Fname
'MsgBox "The address is " & Add & Fname
ThisWorkbook.Names.Add Name:=Fname, RefersToR1C1:=nAdd,
Visible:=True


End Sub

Thanks,
Shishi

 

[Tom Ogilvy]

Sub GenerateNames(Fname As String, Shname As String)
ActiveCell.Name = "'" & Shname & "'!" & Fname
End Sub