relative reference in a macro to define a name

H

Hal Forman

I can't seem to get relative referencing to work. I found the toolbar with
Relative Referencing on it, but even when selected, the macro still has
absolute referencing!

Can anyone tell me what the code might look like to:

select the active cell
move end-down
move right 7 columns
define a name for the resulting range

Many thanks!
 
B

Bob Kilmer

Sub Main()
Dim nms As Names, i As Integer, r As Range

Set r = Range(ActiveCell.End(xlDown), ActiveCell.Offset(0, 7))
Call Names.Add("a_name", "=" & r.Address)

Set nms = ActiveWorkbook.Names
For i = 1 To nms.Count
Debug.Print nms(i).Name
Debug.Print nms(i).RefersToRange.Address
Next

End Sub
 
Top