UDF not handling Numbers stored as text

Please Login or Register  to view this content.

I got this UDF from a Forum Guru here. And it has worked perfectly up til now. It seems the original version doesn’t handle numbers stored as Text. The IsNumeric line gives a “false positive” for numbers stored as text and tries to handle it.
The column field on the pivot table is “JobCode”. The data in this field is Numbers stored as text. When I run the function thru the debugger the “JobCode” is handled by the line

Please Login or Register  to view this content.

The result is “JobCode”, 402

Where I think it should be handled by the line

Please Login or Register  to view this content.

so the result would be “JobCode”, “402”

I attempted to fix the problem by replacing this

Please Login or Register  to view this content.

with this

Please Login or Register  to view this content.

If the Filter = “123” then it would concatenate the two strings and result in “1230” and then Filter.Value + ”0” would not = Filter.Value and it would be handled as a string.
On the other hand if some other Filter was 123, attempting to concatenate a string “0” to it would result in 123 and it would be handled as a Number .

When I run this it runs down to the line

Please Login or Register  to view this content.

And then starts over at the strFormula line and changes the strDataField from “CountofPeople” to “People”. “CountofPeople” is the field name and in the table is actually “Sum of CountofPeople”. The first time thru it uses the correct version “CountofPeople” and then kicks back to the beginning and changes to "People" and it runs thru the code fine and handles the numbers stored as text fine. But still results in an error due to the incorrect DataField. Any idea why that line is kicking the code back to the beginning?
Thanks

Rory, Thanks Again! I replaced this...

Please Login or Register  to view this content.

with this...

Please Login or Register  to view this content.

and no more errors.
Thanks

I spoke too soon... That only works when I point it directly at the pivottable. If I replace the JobCode with a cell reference it fails.
If I use a GETPIVOTDATA( and point JobCode to cell A1 which contains '605 it works fine.
If I use datafrompivot, it fails.

Please Login or Register  to view this content.

I am stepping thru the Function and when it gets to JobCode it steps down to

Please Login or Register  to view this content.

then on F8 it skips down to

Please Login or Register  to view this content.

and returns 605

I have looked at the cell format and it is formatted as text.
It works fine when used in the GETPIVOTDATA function,
and I typed

Please Login or Register  to view this content.

in the immediate window and it returned String.

I am not sure what to try next.

That did it! Thanks again, Rory.