How to define a relative named range in VBA

  • Thread starter Thread starter John Broderick
  • Start date Start date
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
 
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
 
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
 
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)
 
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.
 
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)
 
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 :
 
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
Click to expand...
Click to expand...
 
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
Click to expand...
Click to expand...
 
Back
Top