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

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

Since you are mixing text and numbers, VLOOKUP is not finding a match, and since you are using False for the 4th argument, VLOOKUP returns an error, and that error is what is causing the type mismatch reported by VBA. (Similar thing is going on if you use MATCH instead of VLOOKUP.)

To troubleshoot this, it would be helpful to have a sample file.

Shanan212

ASKER

Please see attached file

Problem is that both pivots have their final source as text

But one pivot is showing text (the looking up pivot) whereas other (destination) is showing numbers (both fields are actually text)

So I want the destination to be treated as text (since, down the range in the table, there are text) Book1.xlsm

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

Since you are mixing text and numbers, VLOOKUP is not finding a match, and since you are using False for the 4th argument, VLOOKUP returns an error, and that error is what is causing the type mismatch reported by VBA. (Similar thing is going on if you use MATCH instead of VLOOKUP.)

To troubleshoot this, it would be helpful to have a sample file.