ReferesTo

[Joel]

Can somebody explain why ReferesTo works in column A and Not in Column B

LastRowA = .Range("A" & Rows.Count).End(xlUp).Row
Set NameRange = .Range("A1:A" & LastRowA)
NameStr = Mid(NameRange.Name.RefersTo, 2)
Set DateRange = .Range("B1:B" & LastRowA)
DateStr = Mid(DateRange.Name.RefersTo, 2)

 

[Barb Reinhardt]

When you define a named range, you define two things.

1) The Name
2) The address (or a formula that defines the address).
or
A constant

The RefersTo part is #2.

 

[Barb Reinhardt]

Oops, I misread your question. What do you get if you do a debug on the
Mid() parts of your code?

Barb Reinhardt

 

[Joel]

did you try the code? I do not have a named range and the NameStr works
perfectly and the DateStr fails?????????????????????????????

The two NameStr and DateStr are identical except one is in Column A and the
other is in Column B.

 

[Dave Peterson]

It's a coincidence.

You already have a defined name (maybe manually, maybe a dynamic name???) that
coincides with the range in column A.

So NameRange.Name returns something like:
=Sheet1!$A$1:$A$12

But you don't have an existing name that coincides with the range described in
your code.

So you got lucky by getting the column A version to work (since it did what you
wanted).

Or you got lucky by getting the column B version to fail (so you don't rely on
this).