Filter Pivot Results In Crash

[MINCUS1308]

HI :)
The function GuessAMachine is passed an ItemNumber and a Quantity as strings.
I modify the Quantity string, clear the pivot filters, and then attempt to filter the pivot using these two strings (ItemNumber and Quantity).
And it works like a champ if I'm lucky - but more times than not Excel "Stops working" lol. Excel alerts me that it has stopped working and is trying to find a solution. The application closes and then restarts with a new workbook.

When debugging - excel bails right as I try to filter the pivot table. The best I can guess is that what I'm attempting to filter by is not an item listed in that filter.
but I'm not entirely sure.

Should I ( / how do I ) check if the item exists prior to attempting to filter the pivot table?
Also criticism on my crappy coding will be well received. I'm self taught and I can use all the criticism I can get.


Function GuessAMachine(ItemNumber As String, Quantity As String)
'ADD THE REST OF THE QUANTITY STRING FOR FILTERING PURPOSES
Quantity = Quantity + ".00"
'SET PIVOT FILTERS TO ITEM OF INTEREST
'CLEAR ALL FILTERS
Worksheets("Pivot").Range("A1").PivotTable.ClearAllFilters
'SET THE FILTERS
Worksheets("Pivot").Range("A1").PivotTable.PivotFields("Item number").CurrentPage = ItemNumber
Worksheets("Pivot").Range("A1").PivotTable.PivotFields("Quantity").CurrentPage = Quantity
'SETUP FOR GUESS
Sheets("Pivot").Select
CountOfTAWC = 0
LastCountOfTAWC = 0
MyMachineGuess = ""
MyConfidence = 0
i = 5
'FIND MOST LIKELY MACHINE BASED ON HISTORICAL DATA
Do Until Cells(i, 1).Value = "Grand Total"
CountOfTAWC = CInt(Worksheets("Pivot").Range("A1").PivotTable.GetPivotData("Count of TA WC", "TA WC", CStr(Cells(i, 1).Value)))
If CountOfTAWC > MaximumOfCountOfTAWC Then
MaximumOfCountOfTAWC = CountOfTAWC
MyMachineGuess = CStr(Cells(i, 1).Value)
MyConfidence = (MaximumOfCountOfTAWC * 100) / CInt(Worksheets("Pivot").Range("A1").PivotTable.GetPivotData("TA WC"))
End If
i = i + 1
Loop
'MAKE MACHINE SUGGESTION
If MyConfidence < 75 Then
With Worksheets("Settings").Range("Table3")
Set C = .Find(CStr(MyMachineGuess), LookIn:=xlValues, LOOKAT:=xlWhole, SEARCHORDER:=xlByRows, MatchCase:=True)
If Not C Is Nothing Then
GuessAMachine = CStr(C.Offset(0, 1).Value)
Else
GuessAMachine = MyMachineGuess
End If
End With

Else
GuessAMachine = MyMachineGuess
End If
End Function

[mdmackillop]

Can you post a sample workbook?

[Paul_Hossler]

... and with some data and the pivot table and the values that don't work

[MINCUS1308]

Ok, don't laugh at the code - its pretty rough lol
A change event in "Production Schedule" cell C16 will trigger the failure. (just double click and then click out)
In contrast, a change event in "Production Schedule" cell C6 will execute without error.

[MINCUS1308]

Can you post a sample workbook?
Yes.

[mdmackillop]

I looked at this but couldn't see the cause. I would try copying and pasting data and code to a new workbook.

[MINCUS1308]

RESOLUTION:
Attempting to filter my pivot table like this:

Sheet7.PivotTables("PivotTable1").PivotFields("Quantity").CurrentPage = CStr(Quantity)
Would cause excel to crash intermittently.

Turns out the pivot table field held the desired filter 'Quantity' in both a number and a text format.
I noticed this as I tried to manually filter a known problem causer. (the value appeared twice in the list of available filters)

My fix: Convert all possible integer values in that data column to an actual number.

[mdmackillop]

Thanks for posting your solution.

[abrokentenor]

Created an account solely to thank the OP for posting his solution. It worked for me; I was working on a macro-free worksheet and it crashed every time I tried to de-select "show all" on the lead field. Thank you!

[MINCUS1308]

No doubt - Id be nothing if not for the assistance of the mentors on this site.