Retrieve pivotitem range

[Guest]

I am trying to retrieve the cell which contains the pivotitem. I am trying to
do this using datarange. But everytime i try to the run the following code,
it gives an "Application defined or Object defined error" .

I tried debugging and it states that the application is unable to find the
datarange. No idea why this happens. Is there a way around this to find the
range.



Code

Function returnIL5Cell(ByVal plitem As PivotField) As Range

Dim rng As Range
Dim pl As PivotItem

Set pl = plitem.PivotItems(2)
rng = pl.DataRange.select

response = MsgBox(rng.Value, vbOKCancel)

End Function


thanks & regards,

Nikhil.

 

[George Nicholson]

My guess would be you want either something like
Set rng = pl.DataRange
rng.Select
or
pl.DataRange.Select
Set rng = ActiveSheet.Selection

but I'm pretty sure you can't make a range assignment and select it in the
same breath.

I've read that "Set" has become unnecessary in XL2007, but if you aren't
using that version it's mandatory when assigning an object variable like
rng.

HTH,