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? |
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? |
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? |
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? |
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? |
All times are GMT +1. The time now is 11:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com