Assigning a variable to RefersTo in Excel

[tbarthen]

I have a procedure to extract the initials from the name in a cell, and then assign the initials to a named range in the workbook. Originally I had the range as a hidden cell, so for my last line in this procedure, I just had:

Worksheets("Fix Checklist&quot.Range("initials&quot = UCase(init)

Range("initials&quot referred to hidden cell A100.

Well now I would like to change this named range to not have a cell reference, but rather a value. So instead of setting the "Refers To" for the range name to a cell reference -- "=$A$100", I would like to set it to the value of my variable (in this procedure) called init.
Therefore, I changed the last line of the procedure to:

ActiveWorkbook.Names.Item("initials&quot.RefersTo = init

Now I get the following error on that line of code:

Run-time error '1004':
Application-defined or object-defined error

Anyone know how to make this work?



Private Sub Extract_Initials()
Dim i As Long
Dim txt As String
Dim x() As String
Dim init As String
init = ""
txt = Worksheets("Fix Checklist&quot.Range("YourName&quot
x() = Split(txt, " &quot
For i = 0 To UBound(x)
init = init + (Left(x(i), 1))
Next i
init = UCase(init)
ActiveWorkbook.Names.Item("initials&quot.RefersTo = init
End Sub

 

[SkipVought]

Hi,

If the Range that you are referring to is named MyRange, then

RefersTo:=Range(MyRange).Address(External:=True)

the external argument includes the sheet reference.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tbarthen]

Hmmmm........I'm not sure how your answer applies to my procedure Skip, but anyways, I figured it out. I had to remove the word "Item" from the code. this line works:

ActiveWorkbook.Names("initials&quot.RefersTo = init


Thanks for responding to my question though.