[SOLVED] UDF calls fine in VBA, but returning blanks when called in worksheet

UDF calls fine in VBA, but returning blanks when called in worksheet

I have a function in a module that runs fine when called in a Sub, but when used in a worksheet I get nothing but blanks. The functions is meant to have 3 inputs. One input determined the worksheet to check, and then find the row matching the other 2 pieces of criteria. I used a sub that called the function and provided a msgbox with the result, which was correct. But in the worksheet the same input resulted in a blank cell.

Function ToolStatus(PartNumber As String, Model As String, Number As Integer) As String
Dim SearchSheet As Worksheet
Dim PN As Integer
Dim MdlCol As Integer
Dim Mdl As String
Dim Result As Integer
Dim FinalRow As Integer
Dim i As Integer
Application.ScreenUpdating = False
Select Case True
    Case Number < WorksheetFunction.CountA(Sheet2.Range("B:B")) And Model = "1A"
        Set SearchSheet = Sheet2
        PN = 2
        MdlCol = 4
        Mdl = "1A"
        Result = 19
    Case Number < WorksheetFunction.CountA(Sheet2.Range("B:B")) And Model = "1B"
        Set SearchSheet = Sheet2
        PN = 2
        MdlCol = 5
        Mdl = "1B"
        Result = 19
    Case Number < WorksheetFunction.CountA(Sheet2.Range("B:B")) And Model = "1C"
        Set SearchSheet = Sheet2
        PN = 2
        MdlCol = 6
        Mdl = "1C"
        Result = 19
    Case Number < WorksheetFunction.CountA(Sheet3.Range("B:B")) And Model = "1A"
        Set SearchSheet = Sheet3
        PN = 3
        MdlCol = 17
        Mdl = "-1A"
        Result = 4
    Case Number < WorksheetFunction.CountA(Sheet3.Range("B:B")) And Model = "1B"
        Set SearchSheet = Sheet3
        PN = 3
        MdlCol = 18
        Mdl = "-1B"
        Result = 4
    Case Number < WorksheetFunction.CountA(Sheet3.Range("B:B")) And Model = "1C"
        Set SearchSheet = Sheet3
        PN = 3
        MdlCol = 19
        Mdl = "-1C"
        Result = 4
End Select
SearchSheet.Select
FinalRow = Cells(Rows.Count, 2).End(xlUp).Row

For i = 2 To FinalRow
    If Cells(i, PN) = PartNumber And Cells(i, MdlCol) = Mdl Then
        ToolStatus = Cells(i, Result).Value
        Exit For
    End If
Next i
Application.ScreenUpdating = True
End Function

Solution

SearchSheet.Select

You cannot do this in a function called as a UDF. See https://support.microsoft.com/en-us/help/170787/description-of-limitations-of-custom-functions-in-excel for details.

You don't need to select the sheet though - just use

FinalRow = SearchSheet.Cells(Rows.Count, 2).End(xlUp).Row

(and similiar for the following code)