Link to home
Start Free TrialLog in
Avatar of Shanan212
Shanan212Flag 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
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

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.
Avatar of 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
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks! That worked like a charm!