Getting Name value when value is a constant
Test out your string in the immediate window - as you see, it doesn't
produce what you expected. See the revision that does:
thename = "Harry"
'Your string modified to include the missing &
? "=" & """ & TheName &
=" & TheName & "
' Modified string
? "=" & """" & thename & """"
="Harry"
Sub Tester4()
Dim thename As String
Dim name1 As String
thename = "Harry"
ActiveWorkbook.Names.Add Name:="ShortName", _
RefersToR1C1:="=" & """" & thename & """"
name1 = Evaluate(Names("ShortName").RefersTo)
Debug.Print name1
End Sub
produced Harry
Regards,
Tom Ogilvy
Quote:
> I'm trying to assign a constant (in this case a string) to a range name.
> The reason is that I first run some macros that find a name. Then the
code
> stops. At a later time I run some other macros that need that name. At
> that time I need to extract that name. I tried:
> TheName = FoundName.Offset(0, 1).Value
> MsgBox "TheName is " & TheName 'Make sure I have the right name
> ActiveWorkbook.Names.Add Name:="ShortName", _
> RefersToR1C1:="=" & """ & TheName """
> Name1= Evaluate(Names("ShortName").RefersTo)
> Let's say TheName is Harry. I want Name1 to be Harry. The line that adds
> the name Shortname has a syntax error. When I change that to hard code
> Harry, then the Name1 line produces a Type Mismatch error How can I
assign
> TheName to the range name ShortName and later extract the name itself
> (Harry). Thanks for your help. Otto