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)

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.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Joel" wrote:

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)

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

Barb Reinhardt



"Joel" wrote:

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)

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.

"Barb Reinhardt" wrote:

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.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Joel" wrote:

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)

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).

Joel wrote:

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)

--

Dave Peterson