More range name issues

[Guest]

I have the following code:

Debug.Print ActiveCell.Offset(0, 1).Value
sRangeName = "PlanYear" & ActiveCell.Offset(0, 1).Value
Debug.Print sRangeName
Debug.Print "Range Value = ", Range(sRangeName).Value


ActiveCell.Offset(0,1).value = 1
the range name "PlanYear1" has a value of zero right now. The definition
shows

=0

at this point in time.

AWS is the activesheet.
sRangeName is a string.

WHAT am I missing?

Thanks

 

[Earl Kiosterud]

Barb,

Seems to work OK here (Excel 2002). I have cell A1 named PlanYear, and it
contains 0. When I run the code I get this in the Immediate window:

1
PlanYear1
Range Value = 0

If I put 55 in A1, I get:

1
PlanYear1
Range Value = 55

I'm confused by "The definition shows =0." Seems to me the definition of
the range name should show the cell address, regardless of its contents at
the moment. Have I misunderstood?

 

[Bob Phillips]

Are you on the same sheet? Maybe try qualifying with the sheet.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

I'm actually working out of a worksheet selection change event so it should
know the sheet. It's a workbook range as opposed to a worksheet range.
I've tried a bunch of things with no success. Any other suggestions?
Thanks

 

[Guest]

I wonder if this might be my problem (where I'm defining the range) within
user form code.


RangeName = "PlanYear" & ActiveCell.Offset(0, 1).Value
Seriesrange = Me.cboPlanYear.Value
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:="=" & Seriesrange

RangeName = "PlanMonth" & ActiveCell.Offset(0, 1).Value
Seriesrange = Me.cboPlanMonth.Value
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:="=" & Seriesrange

Year is in form 2001, 2002, 2003
Month is in form January, February, March

 

[Guest]

Bob,

This is not a range name that's tied to a range on any sheet. It's a
constant that's got a range name. I suspect that might make a difference.
Any suggestions?

Barb

 

[Guest]

I'm getting an error on this line

Debug.Print "PlanDate1", Range("PlanDate1").Value

when I go into the worksheet and enter =PlanDate1, I have NO problem.

The error is Runtime Error 1004,
method Range of object worksheet failed.

Remember, PlanDate1 is defined to be
=1
in the range name definitions sheet.

 

[Dave Peterson]

Not all names refer to ranges.

Maybe you can pick something out of this:

Dim RangeName As String
Dim SeriesRange As String

RangeName = "PlanYear" & ActiveCell.Offset(0, 1).Value
'Seriesrange = Me.cboPlanYear.Value
SeriesRange = "2000"
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:="=" & SeriesRange

Debug.Print "Range Value = ", ActiveWorkbook.Names(RangeName).Value

Debug.Print "Range Value = ", _
Evaluate(ActiveWorkbook.Names(RangeName).Value)

 

[Bob Phillips]

If it is not a range that the name refers to, you cannot use a range object.

Access it via the RefersTo property

Debug.Print Activeworkbook.Names("PlanDate1").RefersTo

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)