create range name in the active cell
 

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

create range name in the active cell
 

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.

jeff wrote:

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

create range name in the active cell
 

sub namecell()
activecell.name="tempgas1"
end sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jeff" wrote in message
...
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

create range name in the active cell
 

On Apr 16, 12:03*pm, Dave Peterson wrote:
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.





jeff wrote:

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

create range name in the active cell
 

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

"jeff" wrote in message
...
On Apr 16, 12:03�pm, Dave Peterson wrote:
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.





jeff wrote:

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