MrExcel Publishing
Your One Stop for Excel Tips & Solutions

what is wrong with this line?


Posted by Steve on September 21, 2001 5:42 AM

I am trying to name a range using the text in a certain cell. Could someone tell me why this line won't work and what might work instead?


ActiveWorkbook.Names.Add Name:=Sheets("Employer Entry").Range("F20").Value


Thanks again,


Steve


Posted by Barrie Davidson on September 21, 2001 5:53 AM

Steve, try using a variable to hold the data. Something like this

Sub YourMacro()
dim rangeName As String
'Your code
rangeName = Sheets("Employer Entry").Range("F20").Value
ActiveWorkbook.Names.Add Name:=rangeName
End Sub

Regards,
Barrie
Barrie Davidson

Posted by Steve on September 21, 2001 6:00 AM

Barry,
Thanks for the suggestion. I tried that but the macro keeps hanging on the line ActiveWorkbook.Names.Add Name:=rangeName

RunTime Error '1004':
Application-defined or object-defined error

Any more thoughts,
Steve Sub YourMacro()

Posted by Barrie Davidson on September 21, 2001 6:08 AM

Steve, forgot to ask, what address are you trying to name?

Barrie

Posted by Steve on September 21, 2001 6:12 AM

Barry,

In my macro, I create a sheet and then highlight a range. The problem happens when I try to name the range. It should be the same name as the text in F20 on a sheet named Employer Entry. Also, the range name is the same as the sheet name I just created.

Posted by Steve on September 21, 2001 6:33 AM

Almost got it

Barry,
I almost have it. At least now it does not stop the macro. I have changed my line to read:

ActiveWorkbook.Names.Add Name:=rangeName, RefersToR1C1:= _
"=rangeName!R15C3:R25C4"

The only problem now is the word "rangename" shows up as the range name instead of the word that rangename represents. Can you fix my syntax?

Steve

Posted by Barrie Davidson on September 21, 2001 7:05 AM

In my macro, I create a sheet and then highlight a range. The problem happens when I try to name the range. It should be the same name as the text in F20 on a sheet named Employer Entry. Also, the range name is the same as the sheet name I just created. :

Steve, try this instead

Sub YourMacro()
dim rangeName As String
Dim namedSelection As String

'Your code
rangeName = Sheets("Employer Entry").Range("F20").Value
namedSelection = "=" & ActiveSheet.Name & "!" & Selection.Address
ActiveWorkbook.Names.Add Name:=rangeName, RefersTo:=namedSelection
End Sub

Barrie
Barrie Davidson

Posted by Steve on September 21, 2001 7:59 AM

Great!

Thanks Barry,
It works great!

Steve