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

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

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" <jbroderick@rfintegration.com> wrote in message
news:O9ZxYhzBGHA.3292@TK2MSFTNGP09.phx.gbl...
> 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
>
>
>
>
>

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" <jbroderick@rfintegration.com> wrote in message
news:O9ZxYhzBGHA.3292@TK2MSFTNGP09.phx.gbl...
> 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
>
>
>
>
>

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

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" <jbroderick@rfintegration.com> wrote in message
news:e6ZFsC0BGHA.3292@TK2MSFTNGP09.phx.gbl...
> 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
>
>

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

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" <xrrcvgpbby@puryyb.ay> wrote in message
news:xn0ebc3dm7j815n00akeepitcoolnl@news.microsoft.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 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

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

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" <jbroderick@rfintegration.com> wrote in message
news:O9ZxYhzBGHA.3292@TK2MSFTNGP09.phx.gbl...
> 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
>
>
>
>
>