How to define a relative named range in VBA

J

John Broderick

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
 
C

Chip Pearson

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
 
J

jtp

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
 
B

Bob Phillips

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

Tim Williams

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

John Broderick

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
 
B

Bob Phillips

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

keepITcool

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

Bob Phillips

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 said:
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
 
D

Dave Peterson

Or in code (missed that):

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

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

John said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top