troubleshooting Question

Pivot Tables, Vlookup and Type Errors

Avatar of Shanan212
Shanan212Flag for Canada asked on
Microsoft Excel
4 Comments1 Solution447 ViewsLast Modified:
        For Each pi In pt.PivotFields("PAYOR ID").PivotItems
            x = Application.VLookup(CStr(pi.Value), ActiveSheet.Range("V3:V500"), 1, False)
            If CStr(pi.Value) = CStr(x) Then
                  pi.Visible = True
            Else
                  pi.Visible = False
            End If
        Next pi

Hi,

I am having a type mismatch error on the above code set.

The range "V3:V500" and pi.value are both seperate pivots...which ultimately connect to same source data which is text.

Now the function would not work. The first value is (pi.value) '1010' and this value is not in the range.

So I put in a value 1010 below the pivot on column V, it would not pick it up. When I put '1010 (hence text) it would pick it up.

How do I go on to make excel believe that both pivots contains text? (or atleast make the column V text? - again, column V contains pivot table)

I am asking the range to be converted into text because I know that pi.value becomes text later on (such that there are other values such as BR001, etc)

Thanks!

PS: Application.match also does the same as it treats one pivot-table as text and other as number
Temp.PNG
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros