create range name in the active cell

[jeff]

I've tried using the recorder, but it gives the exact cell location. I
don't want that. I want the range name to be created where ever the
active cell might be.
Here's what I got when I used the recorder:

Application.Goto Reference:="DateColumn1"
Selection.End(xlDown).Select
Range("A22").Select
ActiveWorkbook.Names.Add Name:="TempGas1", RefersToR1C1:="='V 3'!
R22C1"

I don't want the part
RefersToR1C1:="='V 3'!R22C1"
in the coding, but it doesn't work without it. The way it shows above,
it goes to an exact cell location.

What I want in that last line is to name the range where ever the
active cell is.
Nothing else.
Can anyone help?
Thanks
jeff

 

[Dave Peterson]

Dim myRng as range
Application.Goto Reference:="DateColumn1"
set myrng = selection.end(xldown)
myrng.name = "TempGas1"

This is just a single cell, right?

You could use:
selection.end(xldown).name = "TempGas1"

But I like using a range variable -- just in case the range gets more complex
and I need to modify it.

 

[Don Guillett]

sub namecell()
activecell.name="tempgas1"
end sub

 

[jeff]

Dim myRng as range
Application.Goto Reference:="DateColumn1"
set myrng = selection.end(xldown)
myrng.name = "TempGas1"

This is just a single cell, right?

You could use:
selection.end(xldown).name = "TempGas1"

But I like using a range variable -- just in case the range gets more complex
and I need to modify it.









--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks Dave
I Googled it, and found another solution that also seems to work.

Application.Goto Reference:="DateColumn1"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveWorkbook.Names.Add Name:="TempGas1", RefersTo:=ActiveCell


I guess I was trying to get it without the RefersTo part of the
statement. I didn't know you could just put ActiveCell in there.
Yes, this will only be 1 cell in the named range.
Your solution works, also.
I appreciate your help.
jeff

 

[Don Guillett]

should work from anywhere in the active workbook

Sub gothereandandnameoffsetcell()
Application.Goto "gothere"
ActiveCell.End(xlDown).Name = "wentthere"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Dim myRng as range
Application.Goto Reference:="DateColumn1"
set myrng = selection.end(xldown)
myrng.name = "TempGas1"

This is just a single cell, right?

You could use:
selection.end(xldown).name = "TempGas1"

But I like using a range variable -- just in case the range gets more
complex
and I need to modify it.









--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks Dave
I Googled it, and found another solution that also seems to work.

Application.Goto Reference:="DateColumn1"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveWorkbook.Names.Add Name:="TempGas1", RefersTo:=ActiveCell


I guess I was trying to get it without the RefersTo part of the
statement. I didn't know you could just put ActiveCell in there.
Yes, this will only be 1 cell in the named range.
Your solution works, also.
I appreciate your help.
jeff