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
pi.Visible = False
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)
PS: Application.match also does the same as it treats one pivot-table as text and other as number