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.