Getting Name value when value is a constant 
Author Message
 Getting Name value when value is a constant

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



Sun, 27 Jun 2004 22:03:49 GMT  
 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



Sun, 27 Jun 2004 22:24:56 GMT  
 Getting Name value when value is a constant
Tom
    You've helped me again.  Posting questions in these newsgroups is like
digging for gold in Ft. Knox.  Thanks again for you help.  Otto

Quote:
> 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



> > 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



Sun, 27 Jun 2004 23:27:49 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Getting constant values into a worksheet

2. Get named constants value

3. I am getting #value when I write my function

4. Retreive value of constant range name

5. Get Named constants value

6. getting values form two cells so both values show in one cell

7. getting the selected value in a listbox determine the values in another listbox

8. Getting MAX value from aggregate value

9. getting the value of a named cell

10. Getting Top Value & Names off a form


 
Powered by phpBB® Forum Software © phpBB Group