naming ranges

[turtlejack]

I am wanting to name a range based on current range selection..
any help would be appreciated.

 

[mattj63]

You can do this in worksheet using the define names menu item. To do it in code you need to dimension a Range variable. You can then use

Set MyRange = Range("A1","A6")

to save the range under the range variable.

 

[turtlejack]

here is a sample of code I was working on.
I need "myvar" to return the value of the cell, therefore dictating the range address.

Sub namerange()

Dim myvar As Integer
Range("A5").Select
myvar = [f50]
ActiveWorkbook.Names.Add Name:="keys", RefersToR1C1:="=KEYS!R5C1:R(myvar)C19"

End Sub

 

[dc20]

Try this...

Sub namerange()
 
    Dim myvar As Integer
    Range("A5").Select
    myvar = [f50]
    ActiveWorkbook.Names.Add Name:="keys", RefersToR1C1:="=KEYS!R" & myvar & "C1:R6C19"
   
End Sub

 

[DaleWatson123321]

Clarity is obvious very helpful.

Where you say...
I need "myvar" to return the value of the cell.

...Are you referring to an actual value entered into the cell, or the cell address?


Aside from the above question, the following will create a name based on your initial posting. It creates the name "keys" based on the current selection. If you're only wanting to create a range name on the existing sheet based on the current selection, then the following is all you'll need.

Selection.Name = "keys"

Regards, Dale Watson

 

[turtlejack]

I need the value in the cell to be what is passed to my var..

 

[PHV]

Replace this:
ActiveWorkbook.Names.Add Name:="keys", RefersToR1C1:="=KEYS!R5C1:R(myvar)C19"
By this:
ActiveWorkbook.Names.Add Name:="keys", RefersToR1C1:="=KEYS!R5C1:R" & myvar & "C19"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

.. or, more simply, as shown by Dale ..

[blue][tt] Cells([F50], 19).Name = "keys"[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[turtlejack]

I tried all of the above, and neither code was what I was looking for..
However,I did find some code that does what I need.
Thanks so much for all your help.

 

[PHV]

However,I did find some code that does what I need
Thanks for sharing.
Can you please post this code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[turtlejack]

my apologies
the following code is the code I used.It works fine. i did find it on a webpage somewhere also.. but,its tedious looking for things like that on webpages.This forum, as well, as its users are a great asset..
again, my apologies..
and Thanks for the code.

Selection.Name = "keys"

 

[PHV]

DaleWatson123321 gave you this tip.

 

[turtlejack]

yes,I didnt call names, but am thanking everyone in general For the code, as well, as the efforts.. you all are great