| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
More range name issues
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More range name issues
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? -- Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "Barb Reinhardt" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More range name issues
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) "Barb Reinhardt" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More range name issues
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 "Bob Phillips" wrote: 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) "Barb Reinhardt" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More range name issues
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 "Bob Phillips" wrote: 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) "Barb Reinhardt" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More range name issues
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 "Bob Phillips" wrote: 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) "Barb Reinhardt" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More range name issues
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. "Bob Phillips" wrote: 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) "Barb Reinhardt" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More range name issues
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) Barb Reinhardt wrote: 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 "Bob Phillips" wrote: 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) "Barb Reinhardt" wrote in message ... 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 -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More range name issues
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) "Barb Reinhardt" wrote in message ... 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 "Bob Phillips" wrote: 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) "Barb Reinhardt" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Duplicate Range Names by worksheet | Excel Discussion (Misc queries) | |||
| How do you Identify text as a named range in excel | Excel Discussion (Misc queries) | |||
| Macro Range issues (I think) | New Users to Excel | |||
| Array to named range conversion... | Excel Discussion (Misc queries) | |||
| Pivot table range issues from report writer download | Excel Discussion (Misc queries) | |||
Linear Mode
