Named Range not recognised by VBA 2007

[Guest]

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?

 

[Charles Williams]

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

 

[Guest]

Charles,
it returns a range which formula I found at:
http://tushar-mehta.com/excel/charts/straight_lines/index.html

Regards.

 

[Charles Williams]

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

 

[Guest]

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.

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

Charles,
it returns a range which formula I found at:
http://tushar-mehta.com/excel/charts/straight_lines/index.html

Regards.