Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range not recognised by VBA 2007
Created (name Manager) one dynamic range, named: PerfM, a list of 34 Months
and growing each month: Code: PerfM=OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A:$A), 1) Created (name Manager) a second dynamic range, named: Target, a list as long as PerfM filled with the same number, let's say 6: Code: Target=6*ROW(PerfM)/ROW(PerfM) Target is filled with 1 column and 34 rows value 6. My problem is that VBA does not recognize Target as a range: Code: Set myarray = Range("PerfM") is recognized, but Code: Set myarray = Range("Target") is not recognized. Run-time eroor 1004. Method of "Range" object'_Global' failed. Both names have global scope, and are defined in VBA as Public. Any clues? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range not recognised by VBA 2007
Your Target named range looks like a formula rather than a range to me.
So you probably should be using Evaluate instead of Range Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "Beertje" wrote in message ... Created (name Manager) one dynamic range, named: PerfM, a list of 34 Months and growing each month: Code: PerfM=OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A:$A), 1) Created (name Manager) a second dynamic range, named: Target, a list as long as PerfM filled with the same number, let's say 6: Code: Target=6*ROW(PerfM)/ROW(PerfM) Target is filled with 1 column and 34 rows value 6. My problem is that VBA does not recognize Target as a range: Code: Set myarray = Range("PerfM") is recognized, but Code: Set myarray = Range("Target") is not recognized. Run-time eroor 1004. Method of "Range" object'_Global' failed. Both names have global scope, and are defined in VBA as Public. Any clues? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range not recognised by VBA 2007
Charles,
it returns a range which formula I found at: http://tushar-mehta.com/excel/charts...nes/index.html Regards. "Charles Williams" wrote: Your Target named range looks like a formula rather than a range to me. So you probably should be using Evaluate instead of Range Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "Beertje" wrote in message ... Created (name Manager) one dynamic range, named: PerfM, a list of 34 Months and growing each month: Code: PerfM=OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A:$A), 1) Created (name Manager) a second dynamic range, named: Target, a list as long as PerfM filled with the same number, let's say 6: Code: Target=6*ROW(PerfM)/ROW(PerfM) Target is filled with 1 column and 34 rows value 6. My problem is that VBA does not recognize Target as a range: Code: Set myarray = Range("PerfM") is recognized, but Code: Set myarray = Range("Target") is not recognized. Run-time eroor 1004. Method of "Range" object'_Global' failed. Both names have global scope, and are defined in VBA as Public. Any clues? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range not recognised by VBA 2007
As Tushar says: its a Named Formula, not a Named Range.
try something like this: Dim v As Variant v = [Target] regards Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "Beertje" wrote in message ... Charles, it returns a range which formula I found at: http://tushar-mehta.com/excel/charts...nes/index.html Regards. "Charles Williams" wrote: Your Target named range looks like a formula rather than a range to me. So you probably should be using Evaluate instead of Range Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "Beertje" wrote in message ... Created (name Manager) one dynamic range, named: PerfM, a list of 34 Months and growing each month: Code: PerfM=OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A:$A), 1) Created (name Manager) a second dynamic range, named: Target, a list as long as PerfM filled with the same number, let's say 6: Code: Target=6*ROW(PerfM)/ROW(PerfM) Target is filled with 1 column and 34 rows value 6. My problem is that VBA does not recognize Target as a range: Code: Set myarray = Range("PerfM") is recognized, but Code: Set myarray = Range("Target") is not recognized. Run-time eroor 1004. Method of "Range" object'_Global' failed. Both names have global scope, and are defined in VBA as Public. Any clues? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range not recognised by VBA 2007
Well I changed my approach and just filled a range with the same target number:
j = Range("PerfM").Rows.Count For i=1 to j Target(i)=6 Next i This solved it. thx. "Charles Williams" wrote: As Tushar says: its a Named Formula, not a Named Range. try something like this: Dim v As Variant v = [Target] regards Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "Beertje" wrote in message ... Charles, it returns a range which formula I found at: http://tushar-mehta.com/excel/charts...nes/index.html Regards. "Charles Williams" wrote: Your Target named range looks like a formula rather than a range to me. So you probably should be using Evaluate instead of Range Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "Beertje" wrote in message ... Created (name Manager) one dynamic range, named: PerfM, a list of 34 Months and growing each month: Code: PerfM=OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A:$A), 1) Created (name Manager) a second dynamic range, named: Target, a list as long as PerfM filled with the same number, let's say 6: Code: Target=6*ROW(PerfM)/ROW(PerfM) Target is filled with 1 column and 34 rows value 6. My problem is that VBA does not recognize Target as a range: Code: Set myarray = Range("PerfM") is recognized, but Code: Set myarray = Range("Target") is not recognized. Run-time eroor 1004. Method of "Range" object'_Global' failed. Both names have global scope, and are defined in VBA as Public. Any clues? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with named range for a large spreadsheet in Excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 - Find formulas that use a named range | Excel Discussion (Misc queries) | |||
Refering to a named range in a chart: 2007 | Excel Worksheet Functions | |||
Named range reference in VBA for Excel 2007 | Excel Programming | |||
Expand a Named Range in 2007 | New Users to Excel |