How to define a relative named range in VBA
 

For example I want to create a name (CellBelow) that always refers to the
cell below the active cell.
I know how to do this with Insert Name / Define, but how does one do it
within VBA?

Thanks,
JB

How to define a relative named range in VBA
 

Create a defined name that refers to =B1. Note that this differs
from typical defined names in that it uses relative rather than
absolute references (no dollar signs in the formula).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"John Broderick" wrote in message
...
For example I want to create a name (CellBelow) that always
refers to the
cell below the active cell.
I know how to do this with Insert Name / Define, but how does
one do it
within VBA?

Thanks,
JB

How to define a relative named range in VBA
 

If I understand you correctly try this.

-Dim CellBelow as Range

set CellBelow = Activecell.offset(1,0)-

That should be all.



For example I want to create a name (CellBelow) that always refers to
the
cell below the active cell.
I know how to do this with Insert Name / Define, but how does one do
it
within VBA?

Thanks,
JB


--
jtp
------------------------------------------------------------------------
jtp's Profile: http://www.excelforum.com/member.php...o&userid=21132
View this thread: http://www.excelforum.com/showthread...hreadid=495614

How to define a relative named range in VBA
 

With ActiveCell
ActiveWorkbook.Names.Add Name:="CellBelow", _
RefersTo:="=" & .Offset(-.Row + 2, -.Column + 1).Address(False,
False, , True)
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Broderick" wrote in message
...
For example I want to create a name (CellBelow) that always refers to the
cell below the active cell.
I know how to do this with Insert Name / Define, but how does one do it
within VBA?

Thanks,
JB

How to define a relative named range in VBA
 

You'd have to capture the selection change event in the sheet and then
redefine the range to point to the correct cell.

Eg:

ActiveCell.Offset(1,0).Name=RNGNAME

Where RNGNAME is a constant holding the name of your named range.


Tim.

--
Tim Williams
Palo Alto, CA


"John Broderick" wrote in message
...
For example I want to create a name (CellBelow) that always refers to the
cell below the active cell.
I know how to do this with Insert Name / Define, but how does one do it
within VBA?

Thanks,
JB

How to define a relative named range in VBA
 

Thanks Bob,

this does the same thing as Insert Name /Define, which is the original
question I asked.

However I now realize when I use CellBelow on any sheet it always refers to
the original sheet that was active when I defined it.
I want CellBelow to refer to the cell below the active cell on the active
sheet, not the original sheet.

JB

How to define a relative named range in VBA
 

John,

This does it but you need to do a Ctrl-Alt-F9 to get it to recalculate if
the cell below changes

With ActiveCell
ActiveWorkbook.Names.Add Name:="CellBelow", _
RefersToR1C1:="=!R[1]C[0]"
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Broderick" wrote in message
...
Thanks Bob,

this does the same thing as Insert Name /Define, which is the original
question I asked.

However I now realize when I use CellBelow on any sheet it always refers
to
the original sheet that was active when I defined it.
I want CellBelow to refer to the cell below the active cell on the active
sheet, not the original sheet.

JB

How to define a relative named range in VBA
 

Bob,

Caveat:
When calculation is triggered from VBA code, these "global" relative
reference in a named function will give erroneous results as they will
point to the activesheet at the time of recalc.. dont ask me why,
...Jan Karel Pieterse once told me...


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bob Phillips wrote :

John,

This does it but you need to do a Ctrl-Alt-F9 to get it to
recalculate if the cell below changes

With ActiveCell
ActiveWorkbook.Names.Add Name:="CellBelow", _
RefersToR1C1:="=!R[1]C[0]"
End With

How to define a relative named range in VBA
 

Yeah, I think Charles Williams made that same point once.

It ain't perfect, but I couldn't see another way to get anywhere close to
the OP's request.

Bob


"keepITcool" wrote in message
.com...
Bob,

Caveat:
When calculation is triggered from VBA code, these "global" relative
reference in a named function will give erroneous results as they will
point to the activesheet at the time of recalc.. dont ask me why,
..Jan Karel Pieterse once told me...


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bob Phillips wrote :

John,

This does it but you need to do a Ctrl-Alt-F9 to get it to
recalculate if the cell below changes

With ActiveCell
ActiveWorkbook.Names.Add Name:="CellBelow", _
RefersToR1C1:="=!R[1]C[0]"
End With

How to define a relative named range in VBA
 

How about:
Insert|Name|Define
Cellbelow
=INDIRECT("r[1]c",FALSE)



John Broderick wrote:

Thanks Bob,

this does the same thing as Insert Name /Define, which is the original
question I asked.

However I now realize when I use CellBelow on any sheet it always refers to
the original sheet that was active when I defined it.
I want CellBelow to refer to the cell below the active cell on the active
sheet, not the original sheet.

JB

--

Dave Peterson

How to define a relative named range in VBA
 

Or in code (missed that):

ActiveWorkbook.Names.Add Name:="CellBelow", _
RefersToR1C1:="=INDIRECT(""r[1]c"",FALSE)"

Dave Peterson wrote:

How about:
Insert|Name|Define
Cellbelow
=INDIRECT("r[1]c",FALSE)

John Broderick wrote:

Thanks Bob,

this does the same thing as Insert Name /Define, which is the original
question I asked.

However I now realize when I use CellBelow on any sheet it always refers to
the original sheet that was active when I defined it.
I want CellBelow to refer to the cell below the active cell on the active
sheet, not the original sheet.

JB

--

Dave Peterson

--

Dave Peterson