Problem setting PivotCache.DataSource as a range

[Mike Blackman]

Hi All,

What I'm trying to do is check to see if two pivot cache's data source interesect. Using the pivot cache's .SourceData its coming through as "Data!R16C8:R242C11" and I'm having real problems/brain freeze trying to convert this into a range object... can anyone shed any light on this for me?

 

[Andrew Poulsom]

Does this help?

Sub Test()
    MsgBox Application.ConvertFormula("=Data!R16C8:R242C11", xlR1C1, xlA1)
End Sub

 

[RoryA]

One way, assuming pc1 and pc2 as your cache objects:

    Set rng1 = Range(Application.ConvertFormula(pc1.SourceData, xlR1C1, xlA1))
    Set rng2 = Range(Application.ConvertFormula(pc2.SourceData, xlR1C1, xlA1))
    If Not Intersect(rng1, rng2) Is Nothing then
 ' do whatever

 

[Mike Blackman]

Awesome, thanks guys.