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

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

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

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

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