OK, I got it to work, but I'm confused about what I'm seeing. To make it
work, I did two things you suggested: I changed the RefersToR1C1 to RefersTo
and got rid of the replace that removes the dollar signs. Here's what's
confusing me: if I insert a row above the range, the range addresse changes
to accommodate that, just as I want it to. But I would have thought that with
the $ it wouldn't. What am I missing?

thanks, again.


"Peter T" wrote:

By the way, the curious thing to me about
what you're saying is that I got the RefersToR1C1 by recording a macro of
me
naming a range. Which worked fine.

You may have recorded a macro but then you changed it significantly

Referring to your OP, using the recorded macro as a basis and RefersToR1C1,
all would have worked if you had done

RangeAddress = Range("A20:A450").Address(, , xlR1C1)

It would also have worked had you not removed the $'s in an xlA1 style
address, which the macro did not do.

Regards
Peter T

"ppsa" wrote in message
...
OK, thanks, I'll give it a try. By the way, the curious thing to me about
what you're saying is that I got the RefersToR1C1 by recording a macro of
me
naming a range. Which worked fine.

"Peter T" wrote:

From what you say I suspect you don't want a 'relative' name, which is
what
you were attempting to create. Try this -

Sub Test()
[a4].Select
ActiveWorkbook.Names.Add "RelName", "=A2"
ActiveWorkbook.Names.Add "AbsName", "=$A$2"

Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' A5
Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$2

[b6].Select
Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' B7
Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$2

Rows(1).Delete
Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' B7
Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$1
End Sub

Regards,
Peter T


"ppsa" wrote in message
...
I'm not sure I understand what you're asking. After I name the
address, if
the user inserts a row above the range, yes, I need the named range to
move
down. Is that what you mean?

"Peter T" wrote:

Do you have a particular need to name a relative address, in usage
it
will
be relative to the active cell (eg select A1, name A2, select B3 and
the
name will refer to B4). If that's what you want, use RefersTo
instead of
RefersToR1C1.

Regards,
Peter T


"ppsa" wrote in message
...
In the following code, RangeNamePrefix = "Jul071" and
NewSheet.Name =
"July,
2007 - 1"

================================================== =
Dim NewRangeName As String
Dim RefersTo As String
RangeAddress = Replace(Range("DateValues").Address, "$", "")
RefersTo = "='" & NewSheet.Name & "'!" & RangeAddress
NewRangeName = RangeNamePrefix & "DateValues"

ActiveWorkbook.Names.Add Name:=NewRangeName,
RefersToR1C1:=RefersTo
================================================== =

The variables resolve to the following:
RangeAddress: "A20:A450"
RefersTo: "='July, 2007 - 1'!A20:A450"
NewRangeName: "Jul071DateValues"

You would think that after this code runs, the range A20:A450
would be
named
properly, but it's not. When I go to Insert/Name/Define, the range
name is
listed corrctly, but the address is wrong. It has apostrophes
around
the
individual cell addresses, like this:

='July, 2007 - 1'!'A20':'A450'

I'm stumped. Anyone know what's going on here?

Thanks.