relative reference in a macro to define a name

[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!

 

[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

 

[Hal Forman]

Thanks Bob, that worked, and saved me lots of time. I truly appreciate it.

Hal