referencing a named range generated from a pivot table in VBA

beckyinDC

Board Regular
Joined
Mar 24, 2011
Messages
56
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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...
Code:
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,224,613
Messages
6,179,903
Members
452,948
Latest member
Dupuhini

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top