referencing a named range generated from a pivot table in VBA

[beckyinDC]

code snippet:

If Range("ReportDate").Text = Range("Milestone_1st").Text Then
ActiveWorkbook.Names.Add Name:="PassedAsOf_1st", RefersToR1C1:="=" & Range("TotalPassed").Text
ElseIf Range("ReportDate").Text = Range("Milestone_2nd").Text Then
ActiveWorkbook.Names.Add Name:="PassedAsOf_2nd", RefersToR1C1:="=" & Range("TotalPassed").Text

...

what is working:
- referencing range values for report date and milestone date. Those are both referencing date fields on the spreadsheet. With my data it takes me to the Elseif statement.

problem: I get a 1004 error when it executes that statement.

THe syntax appears to be ok- I can replace
& Range("TotalPassed").Text
with
&"55"
...and that works.

Its just a problem with the reference to the named range TotalPassed

The name exists and can be referenced by a formula within a cell on the spreadsheet.

But in the watch window it shows method range of object global failed for Range("TotalPassed").Text

TotalPassed is defined with a GetPivotData command, so maybe Text property doesnt apply? Here is that definition:

=GETPIVOTDATA("Count",'Details by Test Case Category'!$A$3,"Status","Passed")


I am investigating object browser and web resources based upon that premise...so far a few variations of syntax and using Value instead of txt dont work.

any help? thank you in advance!

oh- and I am using Excel2007

 

[AlphaFrog]

I don't have a definative answer. These are just things to try...

Change RefersToR1C1:= to RefersTo:=

What does TotalPassed evaluate to; Text, Number, Date?

As a test, does this work...

strTemp$ = "=" & Range("TotalPassed").Text
If Range("ReportDate").Text = Range("Milestone_1st").Text Then
ActiveWorkbook.Names.Add Name:="PassedAsOf_1st", RefersTo:=strTemp
ElseIf Range("ReportDate").Text = Range("Milestone_2nd").Text Then
ActiveWorkbook.Names.Add Name:="PassedAsOf_2nd", RefersTo:=strTemp
End If

 

[beckyinDC]

OK- thank you! I tried that code, but still errored out the first time I referenced Range("TotalPassed").text...fair enough- thank you for replying...

I am finding this interesting:

looking at the Names collection in Watch list, I am able to compare properties of each named range. I see difference that ranges that just reference a cell have RefersToRange property that then has Text as a property filled in with the value...but the named range generated from a Pivot table did not have that.

There may be a better way to deal with this (using more straightforward VBA code might make sense)...but I found I could create another named range- TotalPassedTemp that references a cell that has a formula that references TotalPassed. Then I can use Range("TotalPassedTemp").text in the formula. The value passes as an integer not as a reference to the pivot table- that is what I wanted.

thanks for the discussion- it helped!