Avatar of Shanan212
Shanan212
Flag for Canada asked on

Pivot Tables, Vlookup and Type Errors

        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

Open in new window


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
Microsoft Excel

Avatar of undefined
Last Comment
Shanan212

8/22/2022 - Mon
Patrick Matthews

As explained in my article https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2637-Six-Reasons-Why-Your-VLOOKUP-or-HLOOKUP-Formula-Does-Not-Work.html, VLOOKUP requires that the value being sought and the values in the first column of the lookup table be the same data type.

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
ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
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.
ask a question
Shanan212

ASKER
thanks! That worked like a charm!
Your help has saved me hundreds of hours of internet surfing.
fblack61