MINCUS1308
05-17-2017, 06:13 AM
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
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